自己和自己比较 像这样的?? select * from tb a where not exists (select 1 from tb name=a.name and id<a.id)
COL 1 COL2 1------2 2------2SELECT MIN(COL1) FROM TB ----1 SELECT MAX(COL1) FROM TB ----2 SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE COL1>T.COL1 AND COL2=T.COL2)-----2--2???
楼主问的可能是CASE WHEN的问题。表1:ID CODE 1 A 2 A 3 A 4 B 5 B 6 C 7 C 8 C 9 C SELECT SUM(CASE CODE WHEN 'A' THEN 1 ELSE 0 END) A的数量, SUM(CASE CODE WHEN 'B' THEN 1 ELSE 0 END) B的数量, SUM(CASE CODE WHEN 'C' THEN 1 ELSE 0 END) C的数量 FROM 表1 GROUP BY ID结果是: A的数量 B的数量 C的数量 3 2 4
你的了解错了哦比如说我有表TA(BH,LX) BH LX 001 A 002 B 006 C 006 D 010 E我要把相同BH 并且不同LX的记录查询出来,这语句怎么写?
SELECT * FROM TA WHERE BH IN (SELECT BH FROM TA GROUP BY BH HAVING COUNT(*)>1)
select BH,LX from tb group by BH,LX having count(BH)>1
CREATE TABLE TBTEST(BH VARCHAR(10), LX VARCHAR(10)) INSERT TBTEST SELECT '001', 'A' UNION ALL SELECT '002', 'B' UNION ALL SELECT '006', 'C' UNION ALL SELECT '006', 'D' UNION ALL SELECT '010', 'E'--DROP TABLE TBTESTSELECT * FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE BH=T.BH AND LX<>T.LX) BH LX ---------- ---------- 006 C 006 D(所影响的行数为 2 行)
select * from tb a where
not exists
(select 1 from tb name=a.name and id<a.id)
1------2
2------2SELECT MIN(COL1) FROM TB
----1
SELECT MAX(COL1) FROM TB
----2
SELECT * FROM TB T WHERE NOT EXISTS(SELECT 1 FROM TB WHERE COL1>T.COL1 AND COL2=T.COL2)-----2--2???
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 C
9 C
SELECT
SUM(CASE CODE WHEN 'A' THEN 1 ELSE 0 END) A的数量,
SUM(CASE CODE WHEN 'B' THEN 1 ELSE 0 END) B的数量,
SUM(CASE CODE WHEN 'C' THEN 1 ELSE 0 END) C的数量
FROM 表1
GROUP BY ID结果是:
A的数量 B的数量 C的数量
3 2 4
你的了解错了哦比如说我有表TA(BH,LX)
BH LX
001 A
002 B
006 C
006 D
010 E我要把相同BH 并且不同LX的记录查询出来,这语句怎么写?
SELECT * FROM TA WHERE BH IN (SELECT BH FROM TA GROUP BY BH HAVING COUNT(*)>1)
INSERT TBTEST
SELECT '001', 'A' UNION ALL
SELECT '002', 'B' UNION ALL
SELECT '006', 'C' UNION ALL
SELECT '006', 'D' UNION ALL
SELECT '010', 'E'--DROP TABLE TBTESTSELECT * FROM TBTEST T WHERE EXISTS(SELECT 1 FROM TBTEST WHERE BH=T.BH AND LX<>T.LX)
BH LX
---------- ----------
006 C
006 D(所影响的行数为 2 行)