2个表
一、SJ_TAB 司机表
SJ_ID,SJ_MC,SJ_CL
2 李四 10
3 王五 10
6 赵六 10二、CL_TAB 车辆表
CL_ID,CL_CPH
10 1991
11 1992
12 1993
13 1994
一车配3司机;当一个车有3个司机的时候,这个车辆不显示
查的结果是:
11 1992
12 1993
13 1994
在线等啊
一、SJ_TAB 司机表
SJ_ID,SJ_MC,SJ_CL
2 李四 10
3 王五 10
6 赵六 10二、CL_TAB 车辆表
CL_ID,CL_CPH
10 1991
11 1992
12 1993
13 1994
一车配3司机;当一个车有3个司机的时候,这个车辆不显示
查的结果是:
11 1992
12 1993
13 1994
在线等啊
from SJ_TAB group by SJ_CL having count(1)<3 )
select a.*
from CL_TAB a
inner join (select SJ_CL from SJ_TAB group by SJ_CL having count(1)=1) b
on a.CL_ID=b.SJ_CL
DROP TABLE #tba
go
CREATE TABLE #tba (sj_id INT,sj_mc VARCHAR(10),sj_cl INT)
INSERT INTO #tba
SELECT 2,N'李四',10 union all
SELECT 3, N'王五',10 union all
SELECT 6, N'赵六',10
IF OBJECT_ID('#tba') IS NULL
DROP TABLE #tbb
GO
CREATE TABLE #tbb(cl_id INT,cl_cph INT)
INSERT #tbb
SELECT 10, 1991 union all
SELECT 11, 1992 union all
SELECT 12, 1993 union all
SELECT 13, 1994SELECT * FROM #tbb WHERE cl_id NOT IN (SELECT sj_cl FROM #tba GROUP BY sj_cl HAVING COUNT(1)=3)/*
cl_id cl_cph
----------- -----------
11 1992
12 1993
13 1994(3 行受影响)
*/
select * from CL_TAB as c where
(select count(*) from SJ_TAB where SJ_CL=c.CL_ID)<3
IF OBJECT_ID('#tba') IS NULL
DROP TABLE #tba
go
CREATE TABLE #tba (sj_id INT,sj_mc VARCHAR(10),sj_cl INT)
INSERT INTO #tba
SELECT 2,N'李四',10 union all
SELECT 3, N'王五',10 union all
SELECT 6, N'赵六',10
IF OBJECT_ID('#tba') IS NULL
DROP TABLE #tbb
GO
CREATE TABLE #tbb(cl_id INT,cl_cph INT)
INSERT #tbb
SELECT 10, 1991 union all
SELECT 11, 1992 union all
SELECT 12, 1993 union all
SELECT 13, 1994select *
from #tbb
where cl_id not in(select SJ_CL from #tba group by SJ_CL having count(1)>1)/*
cl_id cl_cph
----------- -----------
11 1992
12 1993
13 1994