TableA
EmpNo UserNo
5421 54211
5421 54212
5421 54213TableB
UserNo TypeLevel WorkNum
54211 1 200
54212 2 400
54213 3 50000如何查询最终显示结果为
No WorkNum
5421 600
54213 5000 也就是从TableB表中查询工作量统计,查询Typelevel为1和2的而且在TableA中对应的EmpNo一样的求和以TableA表中的EmpNO
和WorkNum显示,其他的TypeLevel对应的信息直接显示
EmpNo UserNo
5421 54211
5421 54212
5421 54213TableB
UserNo TypeLevel WorkNum
54211 1 200
54212 2 400
54213 3 50000如何查询最终显示结果为
No WorkNum
5421 600
54213 5000 也就是从TableB表中查询工作量统计,查询Typelevel为1和2的而且在TableA中对应的EmpNo一样的求和以TableA表中的EmpNO
和WorkNum显示,其他的TypeLevel对应的信息直接显示
union all
select b.UserNo, b.worknum from tableb b where typelevel not in (1,2)
insert into tablea values('5421' , '54211')
insert into tablea values('5421' , '54212')
insert into tablea values('5421' , '54213')
create table tableb(UserNo varchar(10), TypeLevel varchar(10), WorkNum int)
insert into tableb values('54211' , 1 , 200 )
insert into tableb values('54212' , 2 , 400 )
insert into tableb values('54213' , 3 , 50000)
goselect a.EmpNo , sum(WorkNum) WorkNum from tableb b , tablea a where b.typelevel in (1,2) and b.userno = a.userno group by a.EmpNo
union all
select b.UserNo, b.worknum from tableb b where typelevel not in (1,2)drop table tablea , tableb/*
EmpNo WorkNum
---------- -----------
5421 600
54213 50000(所影响的行数为 2 行)
*/
GO
CREATE TABLE TABLEA(EmpNo INT, UserNo INT)
INSERT TABLEA SELECT 5421 , 54211
INSERT TABLEA SELECT 5421 , 54212
INSERT TABLEA SELECT 5421, 54213
IF OBJECT_ID('TABLEB')IS NOT NULL DROP TABLE TABLEB
GO
CREATE TABLE TABLEB(UserNo INT, TypeLevel INT, WorkNum INT)
INSERT TABLEB SELECT 54211 , 1, 200
INSERT TABLEB SELECT 54212 , 2 , 400
INSERT TABLEB SELECT 54213 , 3 , 50000
SELECT EMPNO NO ,SUM(WorkNum )WorkNum FROM TABLEA A INNER JOIN TABLEB B ON A.UserNo=B.UserNo AND B.TypeLevel IN(1,2)GROUP BY EMPNO
UNION ALL
SELECT UserNo NO,WorkNum FROM TABLEB WHERE TypeLevel NOT IN(1,2)
/*NO WorkNum
----------- -----------
5421 600
54213 50000*/
from TableB as A,
TableA AS B
WHERE A.UserNo=b.UserNo
AND A.TypeLevel IN(1,2)
GROUP BY B.EmpNo
UNION
select B.EmpNo,SUM(a.WorkNum) AS CNT
from TableB as A,
TableA AS B
WHERE A.UserNo=b.UserNo
AND A.TypeLevel NOT IN(1,2)
GROUP BY B.EmpNo显示结果5421 600
5421 50000
-----------------
select B.EmpNo,SUM(a.WorkNum) AS CNT
from TableB as A,
TableA AS B
WHERE A.UserNo=b.UserNo
AND A.TypeLevel IN(1,2)
GROUP BY B.EmpNo
UNION
select A.UserNo,a.WorkNum AS CNT
from TableB as A,
TableA AS B
WHERE A.UserNo=b.UserNo
AND A.TypeLevel NOT IN(1,2)
显示结果5421 600
54213 50000
你要哪一个 ?
DECLARE @t TABLE
(EmpNO INT,
UserNo int
)INSERT INTO @t VALUES('5421','54211')
INSERT INTO @t VALUES ('5421','54212')
INSERT INTO @t VALUES ('5421','54213')
DECLARE @TableB TABLE
(UserNo INT,
TypeLevel INT,
WorkNum INT)INSERT INTO @TableB VALUES(54211 , 1 , 200 )
INSERT INTO @TableB VALUES(54212 , 2 , 400 )
INSERT INTO @TableB VALUES(54213 , 3 , 50000 )--查询
SELECT B.EmpNO,SUM(A.WorkNum) WorkNum FROM
(SELECT UserNo,SUM(WorkNum) WorkNum FROM @TableB WHERE TypeLevel IN('1','2') GROUP BY UserNo )A
LEFT JOIN @t B on A.UserNo = B.UserNo
GROUP BY B.EmpNO
UNION
select b.UserNo, b.worknum from @tableb b where typelevel not in (1,2)
GO结果:(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
EmpNO WorkNum
----------- -----------
5421 600
54213 50000(2 行受影响)
DECLARE @t TABLE
(EmpNO INT,
UserNo int
)INSERT INTO @t VALUES('5421','54211')
INSERT INTO @t VALUES ('5421','54212')
INSERT INTO @t VALUES ('5421','54213')
DECLARE @TableB TABLE
(UserNo INT,
TypeLevel INT,
WorkNum INT)INSERT INTO @TableB VALUES(54211 , 1 , 200 )
INSERT INTO @TableB VALUES(54212 , 2 , 400 )
INSERT INTO @TableB VALUES(54213 , 3 , 50000 )--查询结果
SELECT a.EmpNO,SUM(b.WorkNum) FROM @t A,@TableB B
WHERE a.UserNo = b.UserNo AND b.TypeLevel IN ('1','2')
GROUP BY A.EmpNO
UNION
select b.UserNo, b.worknum from @tableb b where typelevel not in (1,2)
GOResult:(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
EmpNO
----------- -----------
5421 600
54213 50000(2 行受影响)
列 'TB_WorkCharResult.TIMECOUNT' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
怎么解决 但是也不能写在gourp by 子句中,因为我要根据EmpNo计算