34,593
社区成员
发帖
与我相关
我的任务
分享
select 表一.班级序号,班级名称,表二.姓名,表二.语文,表三.数学,表二.语文+表三.数学 from 表一,表二,表三 where 表一.班级序号=表二.班级序号 and 表二.班级序号=表三.班级序号 and 表二.姓名=表三.姓名 order by 表一.班级序号,表二.姓名,表二.语文,表三.数学
DECLARE @a TABLE(cId INT,NAME VARCHAR(20))
insert @a select 1 ,'高三一班'
union all select 2 ,'高二三班'
union all select 3 ,'高一二班'
declare @b table(cId int,Name varchar(20),Yw int)
insert @b select 1 ,'张三', 100
union all select 1 ,'李四', 99
union all select 2 ,'王五', 98
declare @c table(cId int,Name varchar(20),Sx int)
insert @c select 1 ,'张三', 98
union all select 2 ,'王五', 87
union all select 3 ,'赵六', 60
SELECT A.cId,a.Name,c.Name Name1,Yw,Sx,Su FROM @a a LEFT JOIN (
SELECT ISNULL(b.cId,c.cId) cId,
ISNULL(b.Name,c.Name) NAME,
Yw,
Sx,
ISNULL(Yw,0)+ISNULL(Sx,0) Su
FROM @b b FULL JOIN @c c ON b.cid=c.cid AND b.name=c.name
) c ON a.cid=c.cid
--result
/*cId Name Name1 Yw Sx Su
----------- -------------------- -------------------- ----------- ----------- -----------
1 高三一班 张三 100 98 198
1 高三一班 李四 99 NULL 99
2 高二三班 王五 98 87 185
3 高一二班 赵六 NULL 60 60
(所影响的行数为 4 行)
*/
select 表一.班级序号,班级名称,表二.姓名,表二.语文,表三.数学,表二.语文+表三.数学
from 表一,表二,表三
where 表一.班级序号=表二.班级序号
and 表二.班级序号=表三.班级序号
and 表二.姓名=表三.姓名
order by 表一.班级序号,表二.姓名,表二.语文,表三.数学
create TABLE a(cId INT,NAME VARCHAR(20))
insert a select 1 ,'高三一班'
union all select 2 ,'高二三班'
union all select 3 ,'高一二班'
create table b(cId int,Name varchar(20),Yw int)
insert b select 1 ,'张三', 100
union all select 1 ,'李四', 99
union all select 2 ,'王五', 98
create table c(cId int,Name varchar(20),Sx int)
insert c select 1 ,'张三', 98
union all select 2 ,'王五', 87
union all select 3 ,'赵六', 60
;with sel as(
select cid,name,yw,0 sx from b union all
select cid,name,0 yw,sx from c
),sel2 as(
select cId,Name,Yw=SUM(yw),sx=SUM(sx) from sel group by cId,Name)
select a.cid,a.name,sel2.name,yw,sx,total=yw+sx from a join sel2 on
a.cid=sel2.cId
select a.班级序号, a.班级名称, isnull(b.姓名, c.姓名) 姓名,语文,数学,ISNULL(语文,0)+ISNULL(数学,0) 总分
from 表二 b
full join
表三 c
on b.班级序号=c.班级序号 and b.姓名=c.姓名
left join 表一 a
on ISNULL(b.班级序号, c.班级序号)=a.班级序号
select isnull(a.班级序号, b.班级序号) 班级序号,
isnull(a.班级名称, b.班级名称) 班级名称,
isnull(a.姓名, b.姓名) 姓名,
isnull(a.语文, 0) 语文,
isnull(b.数学, 0) 数学,
(isnull(语文, 0) + isnull(数学, 0)) 总分
from (select 表一.班级序号 班级序号,班级名称,姓名,语文
from 表一join 表二
on 表一.班级序号 =表二.班级序号) a
full join
(select 表一.班级序号 班级序号,班级名称, 姓名,数学
from 表一 join 表三
on 表一.班级序号 =表三.班级序号) b
on a.班级序号 = b.班级序号
and a.姓名 = b.姓名
select (case when a.班级序号 is null then b.班级序号 else a.班级序号)班级序号,
(case when a.班级名称 is null then b.班级名称 else a.班级名称)班级名称,
(case when a.姓名 is null then b.姓名 else a.姓名) 姓名,
(case when a.语文 is null then 0 else a.语文) 语文,
(case when b.数学 is null then 0 else b.数学) 数学,
(语文 + 数学) 总分
from (select 表一.班级序号 班级序号,班级名称,姓名,语文
from 表一join 表二
on 表一.班级序号 =表二.班级序号) a
full join
(select 表一.班级序号 班级序号,班级名称, 姓名,数学
from 表一 join 表三
on 表一.班级序号 =表三.班级序号) b
on a.班级序号 = b.班级序号
and a.姓名 = b.姓名
SELECT A.CLASSID,A.CLASSNAME,
CASE WHEN B.USERID IS NOT NULL THEN B.USERID ELSE C.USERID END AS USERID,
ISNULL(C.CHINESESCORE,0) AS CHINESESCORE,ISNULL(D.MATHCORE,0) AS MATHCORE,
ISNULL(C.CHINESESCORE,0)+ISNULL(D.MATHCORE,0) AS TOTAL
FROM T_CSDN_ONE A
LEFT JOIN (SELECT CLASSID,USERID
FROM T_CSDN_TWO UNION SELECT CLASSID,USERID FROM T_CSDN_THREE) B ON B.CLASSID=A.CLASSID
FULL JOIN T_CSDN_TWO C ON A.CLASSID=C.CLASSID AND C.USERID=B.USERID
FULL JOIN T_CSDN_THREE D ON D.CLASSID=A.CLASSID AND D.USERID=B.USERID
--表一: 班级序号 班级名称
IF OBJECT_ID('T_CSDN_ONE') IS NOT NULL
BEGIN
DROP TABLE t_csdn_one
END
CREATE TABLE T_CSDN_ONE
(
CLASSID INT IDENTITY(1,1) PRIMARY KEY,
CLASSNAME VARCHAR(50)
)
INSERT INTO T_CSDN_ONE
SELECT '高三一班'
UNION ALL
SELECT '高二三班'
UNION ALL
SELECT '高一二班'
--表二:班级序号 姓名 语文
IF OBJECT_ID('T_CSDN_TWO') IS NOT NULL
BEGIN
DROP TABLE T_CSDN_TWO
END
CREATE TABLE T_CSDN_TWO
(
CLASSID INT,
USERID VARCHAR(20),
CHINESESCORE DECIMAL(12,2)
)
INSERT INTO T_CSDN_TWO
SELECT 1,'张三',100
UNION ALL
SELECT 1,'李四',99
UNION ALL
SELECT 2,'王五',98
--表三 班级序号 姓名 数学
IF OBJECT_ID('T_CSDN_THREE') IS NOT NULL
BEGIN
DROP TABLE T_CSDN_THREE
END
CREATE TABLE T_CSDN_THREE
(
CLASSID INT,
USERID VARCHAR(20),
MATHCORE DECIMAL(12,2)
)
INSERT INTO T_CSDN_THREE
SELECT 1,'张三',98
UNION ALL
SELECT 2,'王五',87
UNION ALL
SELECT 3,'赵六',60
SELECT A.CLASSID,A.CLASSNAME,
CASE WHEN B.USERID IS NOT NULL THEN B.USERID ELSE C.USERID END AS USERID,
ISNULL(B.CHINESESCORE,0) AS CHINESESCORE,ISNULL(C.MATHCORE,0) AS MATHCORE,
ISNULL(B.CHINESESCORE,0)+ISNULL(C.MATHCORE,0) AS TOTAL
FROM T_CSDN_ONE A
FULL JOIN T_CSDN_TWO B ON A.CLASSID=B.CLASSID
FULL JOIN T_CSDN_THREE C ON C.CLASSID=A.CLASSID