CREATE TABLE `fun_test_data` (
`no` bigint(20) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`department` varchar(255) NOT NULL default '',
`boss` varchar(255) NOT NULL default '',
`salary` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`no`)
) ENGINE=InnoDB
DEFAULT CHARACTER SET utf8
COLLATE utf8_unicode_ci;
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('あああ', '営業', 'かかか', 25);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('いいい', '営業', 'かかか', 30);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ううう', '営業', 'かかか', 35);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('えええ', '営業', 'かかか', 23);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('おおお', '営業', 'かかか', 60);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('かかか', '営業', 'かかか', 55);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ききき', '営業', 'かかか', 40);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('くくく', '営業', 'かかか', 34);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('けけけ', '営業', 'かかか', 33);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('こここ', '営業', 'かかか', 52);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('さささ', 'マーケ', 'さささ', 41);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ししし', 'マーケ', 'さささ', 30);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('すすす', 'マーケ', 'さささ', 33);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('せせせ', 'マーケ', 'せせせ', 44);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('そそそ', 'マーケ', 'せせせ', 53);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('たたた', '開発', 'せせせ', 33);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ちちち', '開発', 'せせせ', 39);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('つつつ', '開発', 'せせせ', 44);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ててて', '開発', 'せせせ', 50);
INSERT INTO `fun_test_data`(`name`, `department`, `boss`, `salary`) VALUES ('ととと', '開発', 'かかか', 60);
==============
nullが最小か調べる
select * from x_data order by value
CREATE TABLE IF NOT EXISTS `x_data` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`value` int(11) DEFAULT NULL,
`value2` int(11) DEFAULT NULL,
`v_no_null` int(11) NOT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO `x_data` (`no`, `value`, `value2`, `v_no_null`) VALUES
(1, 100, NULL, 0),
(2, NULL, 200, 0),
(3, NULL, 200, 5),
(4, NULL, 100, 10),
(5, NULL, 300, 0),
(6, 200, NULL, 0),
(7, 200, NULL, 5),
(8, 100, NULL, 10);
==============
CREATE TABLE IF NOT EXISTS `x_bill` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`user_no` bigint(20) NOT NULL DEFAULT '0',
`total_bill` bigint(20) NOT NULL DEFAULT '0',
`date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;
INSERT INTO `x_bill` (`no`, `user_no`, `total_bill`, `date`) VALUES
(1, 1, 1000, '2021-01-01 00:00:00'),
(2, 1, 1500, '2021-01-02 00:00:00'),
(3, 1, 500, '2021-01-03 00:00:00'),
(4, 2, 1100, '2021-01-01 00:00:00'),
(5, 2, 1700, '2021-01-02 00:00:00'),
(6, 2, 800, '2021-01-03 00:00:00'),
(7, 3, 1100, '2021-01-01 00:00:00'),
(8, 3, 1700, '2021-01-02 00:00:00'),
(9, 3, 800, '2021-01-03 00:00:00'),
(10, 3, 800, '2021-01-04 00:00:00');
CREATE TABLE IF NOT EXISTS `x_purchase` (
`no` bigint(20) NOT NULL AUTO_INCREMENT,
`p_no` bigint(20) NOT NULL DEFAULT '0',
`item` bigint(20) NOT NULL DEFAULT '0',
`price` bigint(20) NOT NULL DEFAULT '0',
`unit` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;
INSERT INTO `x_purchase` (`no`, `p_no`, `item`, `price`, `unit`) VALUES
(1, 1, 1, 100, 10),
(2, 2, 1, 100, 10),
(3, 2, 2, 100, 5),
(4, 3, 2, 100, 5),
(5, 4, 3, 300, 3),
(6, 4, 1, 100, 2),
(7, 5, 3, 300, 5),
(8, 5, 2, 100, 2),
(9, 6, 1, 100, 3),
(10, 6, 2, 100, 2),
(11, 6, 3, 300, 1),
(12, 7, 1, 100, 11),
(13, 8, 2, 100, 17),
(14, 9, 3, 300, 2),
(15, 9, 1, 100, 2),
(16, 10, 3, 300, 2),
(17, 10, 2, 100, 2);
結合が増えると多対多がでる、sum(total_bill)は多対多なのでダメ
SELECT name, sum(total_bill) FROM x_employee
RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no
RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no
GROUP BY name ORDER BY sum(total_bill) DESC
1対多のsum(price * unit)の細かい表をの方を使う
SELECT name, SUM(price * unit) FROM x_employee
RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no
RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no
GROUP BY name
ORDER BY SUM(price * unit) DESC
多対多が分かる（結合がツリー状で枝の表は使える(幹と枝で1対多)：田中の購入商品数分の表は正、だが途中の請求額は重複(中間が枝化して多対多)していて使えない）
SELECT * FROM x_employee
RIGHT JOIN x_bill ON x_employee.no = x_bill.user_no
RIGHT JOIN x_purchase ON x_bill.no = x_purchase.p_no