1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
| --------------
START TRANSACTION
--------------
--------------
set session collation_connection = "utf8_general_ci"
--------------
--------------
DROP DATABASE IF EXISTS `base`
--------------
--------------
CREATE DATABASE IF NOT EXISTS `base`
DEFAULT CHARACTER SET `utf8`
DEFAULT COLLATE `utf8_general_ci`
--------------
--------------
DROP TABLE IF EXISTS `country`
--------------
--------------
CREATE TABLE `country`
( `country_key` integer unsigned NOT NULL primary key,
`country` varchar(30) NOT NULL,
`region` varchar(30) NOT NULL
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `country` (`country_key`,`country`,`region`) VALUES
(1,'France',''),
(2,'Germany',''),
(3,'India',''),
(4,'Sweden',''),
(5,'Australia',''),
(6,'United States',''),
(7,'China',''),
(8,'Canada',''),
(9,'Switzerland',''),
(10,'Austria','')
--------------
--------------
select * from `country`
--------------
+-------------+---------------+--------+
| country_key | country | region |
+-------------+---------------+--------+
| 1 | France | |
| 2 | Germany | |
| 3 | India | |
| 4 | Sweden | |
| 5 | Australia | |
| 6 | United States | |
| 7 | China | |
| 8 | Canada | |
| 9 | Switzerland | |
| 10 | Austria | |
+-------------+---------------+--------+
--------------
DROP TABLE IF EXISTS `customer`
--------------
--------------
CREATE TABLE `customer`
( `customer_login_id` integer unsigned NOT NULL primary key,
`organization` varchar(30) NOT NULL,
`country_key` integer unsigned NOT NULL,
CONSTRAINT `FK_COUNTRY` FOREIGN KEY (`country_key`) REFERENCES `country` (`country_key`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
ROW_FORMAT=COMPRESSED
--------------
--------------
INSERT INTO `customer` (`customer_login_id`,`country_key`,`organization`) VALUES
(10000,3,'DE'),
(27000,2,'dede'),
(27384,1,'DE')
--------------
--------------
select * from `customer`
--------------
+-------------------+--------------+-------------+
| customer_login_id | organization | country_key |
+-------------------+--------------+-------------+
| 10000 | DE | 3 |
| 27000 | dede | 2 |
| 27384 | DE | 1 |
+-------------------+--------------+-------------+
--------------
select count(co.country_key) as nbre
from `customer` as cu
left outer join `country` as co
on co.country_key = cu.country_key
WHERE ( (co.country != 'France')
OR (cu.organization != 'DEDE')
)
AND cu.customer_login_id = 27384
--------------
+------+
| nbre |
+------+
| 1 |
+------+
--------------
COMMIT
--------------
Appuyez sur une touche pour continuer... |
Partager