34,594
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT identity(1,1),itemtype VARCHAR(4),username VARCHAR(4),score VARCHAR(4))
INSERT INTO [tb]
SELECT '实际','张三','75分' UNION ALL
SELECT '实际','李四','80分' UNION ALL
SELECT '预估','李四','85分'
go
--预估分数随机么?
select itemtype,username,score from tb
union all
select '预估',username,rtrim(60+abs(CHECKSUM(NEWID()))%41)+'分'
from tb k where not exists(select * from tb where k.username=username and itemtype='预估')
order by username
/*
(3 行受影响)
itemtype username score
-------- -------- --------------
实际 李四 80分
预估 李四 85分
预估 张三 89分
实际 张三 75分*/
create table #(id int,itemtype varchar(10), username varchar(10), score varchar(10))
insert # select 1 ,'实际','张三','75分'
insert # select 2 ,'实际','李四','80分'
insert # select 3 ,'预估','李四','85分'
select '预估',a.username,'60分' from
(select * from # where itemtype='实际') a left join
(select * from # where itemtype='预估') b on a.username=b.username
where b.id is null
username
---- ---------- ----
预估 张三 60分
(1 行受影响)
---------------------------------
-- Author: HEROWANG(让你望见影子的墙)
-- Date : 2010-03-25 16:21:09
-- blog : blog.csdn.net/herowang
---------------------------------
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
go
CREATE TABLE [tb] (id INT,itemtype VARCHAR(4),username VARCHAR(4),score VARCHAR(4))
INSERT INTO [tb]
SELECT 1,'实际','张三','75分' UNION ALL
SELECT 2,'实际','李四','80分' UNION ALL
SELECT 3,'预估','李四','85分'
select itemtype,username,score from tb
union all
select '预估',username,'60分'
from tb t
where not exists(select 1 from tb where username=t.username and itemtype='预估')
order by username
itemtype username score
实际 李四 80分
预估 李四 85分
预估 张三 60分
实际 张三 75分
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[itemtype] varchar(4),[username] varchar(4),[score] varchar(4))
insert [tb]
select 1,'实际','张三','75分' union all
select 2,'实际','李四','80分' union all
select 3,'预估','李四','85分'
select
t.itemtype,t.username,isnull(h.score,t.score) as score
from
(
select distinct [username],r.itemtype,'60分' as score
from [tb] ,(select '实际' as itemtype union select '预估') r
) t left join tb h
on t.itemtype = h.itemtype and t.username = h.username
itemtype username score
-------- -------- -----
实际 李四 80分
实际 张三 75分
预估 李四 85分
预估 张三 60分
(所影响的行数为 4 行)