-- 1查询下面表中 id重复的(通过count计数的方式)

 CREATE TABLE t_demo1(

	id INT

 );

INSERT INTO t_demo1 VALUES(1);

INSERT INTO t_demo1 VALUES(2);

INSERT INTO t_demo1 VALUES(3);

INSERT INTO t_demo1 VALUES(4);

INSERT INTO t_demo1 VALUES(4);

 -- 答案

 SELECT * FROM t_demo1 WHERE id IN(

	SELECT id FROM t_demo1 GROUP BY id HAVING COUNT(id) > 1

	)

-- 2查询出表t_demo1中有但表t_demo2中没有的记录

 CREATE TABLE t_demo2(

	id INT

 );

INSERT INTO t_demo2 VALUES(3);

INSERT INTO t_demo2 VALUES(4);	

SELECT * FROM t_demo1

SELECT * FROM t_demo2
-- 答案

SELECT * FROM t_demo1 WHERE id NOT IN(

	SELECT DISTINCT t2.id FROM t_demo1 t1 RIGHT JOIN t_demo2 t2 ON t1.id != t2.id

)

-- 3 批量更新订单表中的金额

-- 订单表

CREATE TABLE A(

	aid VARCHAR(10) COMMENT '主键id',

	c_time DATE COMMENT '时间',

	total DOUBLE COMMENT '总金额'

);

-- 订单明细表

CREATE TABLE B(

	aid VARCHAR(10) COMMENT '主键id',

	amount DOUBLE COMMENT '货物金额',

	comments VARCHAR(20) COMMENT '购买说明'

);

INSERT INTO A VALUES('D01', '2018-01-01', NULL);

INSERT INTO A VALUES('D02', '2018-01-02', NULL);

INSERT INTO B VALUES('D01', 100, '手机充值');

INSERT INTO B VALUES('D01', 4, 'Q币充值');

INSERT INTO B VALUES('D02', 90, '买迪卡侬');

-- 答案

UPDATE A SET A.total = (SELECT SUM(B.`amount`) FROM B WHERE A.aid = B.aid)

SELECT * FROM A

SELECT * FROM B

UPDATE A SET total = NULL WHERE a.aid = 'D01'

-- 4用一句sql 统计01月份 12月份及全年的订单总金额

CREATE TABLE C(

	id INT COMMENT '主键id' ,

	c_time DATE COMMENT '时间',

	amount DOUBLE COMMENT '订单金额',

	comments VARCHAR(40) COMMENT '备注'

);

INSERT INTO C VALUES(1, '2018-01-01 01:30:00', 300, '衣服');

INSERT INTO C VALUES(2, '2018-01-01 01:30:00', 300, '衣服');

INSERT INTO C VALUES(3, '2018-12-01 01:30:00', 300, '衣服');

INSERT INTO C VALUES(4, '2018-12-01 01:30:00', 300, '衣服');

INSERT INTO C VALUES(5, '2017-12-01 01:30:00', 300, '衣服');

SELECT DATE_FORMAT(c_time,'%Y-%m') FROM c

-- 答案

SELECT DATE_FORMAT(c_time,'%Y%m') AS ct,SUM(amount) FROM c WHERE YEAR(c_time) = '2018' GROUP BY ct,amount

UNION

SELECT DATE_FORMAT(c_time,'%Y') AS ct,SUM(amount) FROM c WHERE YEAR(c_time)