select count(*) from (select * from table1 group by a,b,c,d having count(*)<2) tselect * from table1 group by a,b,c,d having count(*)<2
--1 select count(*) from (select distinct a, b, c, d from tablename) tmp--2 select * from tablename
CREATE TABLE TABLENAME(A VARCHAR(10),B VARCHAR(10),C VARCHAR(10),D VARCHAR(10)) INSERT TABLENAME SELECT 'A1','B1','C1','D1' UNION ALL SELECT 'A12','B1','C1','D1' UNION ALL SELECT 'A13','B1','C1','D1' UNION ALL SELECT 'A1','B1','C1','D1' UNION ALL SELECT 'A2','B1','C3','D1' UNION ALL SELECT 'A2','B1','C3','D1' UNION ALL SELECT 'A1','B1','C1','D1' --SELECT * FROM TABLENAMESELECT COUNT(*) FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1SELECT * FROM TABLENAME WHERE A+B+C+D IN (SELECT A+B+C+D FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1)DROP TABLE TABLENAME
count(*) 加 group by 每次我都得到很多的111111111111111111111111111111111111111 没有总数啊
CREATE TABLE TABLENAME(A VARCHAR(10),B VARCHAR(10),C VARCHAR(10),D VARCHAR(10)) INSERT TABLENAME SELECT 'A1','B1','C1','D1' UNION ALL SELECT 'A12','B1','C1','D1' UNION ALL SELECT 'A13','B1','C1','D1' UNION ALL SELECT 'A1','B1','C1','D1' UNION ALL SELECT 'A2','B1','C3','D1' UNION ALL SELECT 'A2','B1','C3','D1' UNION ALL SELECT 'A1','B1','C1','D1' --SELECT * FROM TABLENAMESELECT SUM(RESULT) FROM ( SELECT 1 AS RESULT FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1 )ISELECT * FROM TABLENAME WHERE A+B+C+D IN (SELECT A+B+C+D FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1)DROP TABLE TABLENAME
use pubs goselect distinct fname ,emp_id from ( select distinct emp_id ,fname from employee )as tb1重复使用 distinct
select count(*) from (select distinct a, b, c, d from tablename) tmp--2
select * from tablename
INSERT TABLENAME
SELECT 'A1','B1','C1','D1' UNION ALL
SELECT 'A12','B1','C1','D1' UNION ALL
SELECT 'A13','B1','C1','D1' UNION ALL
SELECT 'A1','B1','C1','D1' UNION ALL
SELECT 'A2','B1','C3','D1' UNION ALL
SELECT 'A2','B1','C3','D1' UNION ALL
SELECT 'A1','B1','C1','D1'
--SELECT * FROM TABLENAMESELECT COUNT(*) FROM TABLENAME
GROUP BY A+B+C+D
HAVING COUNT(*)=1SELECT * FROM TABLENAME
WHERE A+B+C+D IN (SELECT A+B+C+D FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1)DROP TABLE TABLENAME
没有总数啊
INSERT TABLENAME
SELECT 'A1','B1','C1','D1' UNION ALL
SELECT 'A12','B1','C1','D1' UNION ALL
SELECT 'A13','B1','C1','D1' UNION ALL
SELECT 'A1','B1','C1','D1' UNION ALL
SELECT 'A2','B1','C3','D1' UNION ALL
SELECT 'A2','B1','C3','D1' UNION ALL
SELECT 'A1','B1','C1','D1'
--SELECT * FROM TABLENAMESELECT SUM(RESULT) FROM
(
SELECT 1 AS RESULT FROM TABLENAME
GROUP BY A+B+C+D
HAVING COUNT(*)=1
)ISELECT * FROM TABLENAME
WHERE A+B+C+D IN (SELECT A+B+C+D FROM TABLENAME GROUP BY A+B+C+D HAVING COUNT(*)=1)DROP TABLE TABLENAME
goselect distinct fname ,emp_id from (
select distinct emp_id ,fname from employee
)as tb1重复使用 distinct
print @@rowcount