有如下两个表
表1 fid int ,
mc varchar(200) ,
......
表2 fid int ,
mxid int ,
mc varchar(200),
表1 与表2 通fid关联
要求对表1 中相同的值显示为空
fid mc fid mxid mc
1 001 1 1 0001
2 00002
3 000224
2 002 2 1 00001
2 0254
表1 fid int ,
mc varchar(200) ,
......
表2 fid int ,
mxid int ,
mc varchar(200),
表1 与表2 通fid关联
要求对表1 中相同的值显示为空
fid mc fid mxid mc
1 001 1 1 0001
2 00002
3 000224
2 002 2 1 00001
2 0254
解决方案 »
- 两天数据比较,算出用量,一条SQL
- 又发一次关于求时间差的问题,昨天的有点错误,还请帮忙
- 无法连接sql server
- 某一列满足两个条件怎么查?
- 本地备份数据库(数据库在另一台机子)时想选择数据库服务器设备路径(即保存备份文件的路径),请教如何能选择?
- 运行SP,发生这样的错误:Maximum stored procedure,funtion,trigger,or view nesting levele execcded
- 关于SQLServer的问题,急!
- 下面的SQL查询语句要怎么写,谢谢。
- 软件运行时偶尔连接失败!一直找不到解决办法
- 大力,蚂蚁此处何错?帮忙指点迷津!
- access数据库为什么不支持left join语句?
- 怎么在SQL Server 2000中书写id字段自动增长语句且id 为主键?
DECLARE @b TABLE(fid INT,mxid INT,mc VARCHAR(200))INSERT @a SELECT 1,'001'
UNION ALL SELECT 2,'002'INSERT @b SELECT 1,1,'0011'
UNION ALL SELECT 1,2,'0012'
UNION ALL SELECT 1,3,'001x'
UNION ALL SELECT 2,4,'002z'
UNION ALL SELECT 2,5,'002n'SELECT a.fid,case when exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then '' else a.mc end mc,
b.fid,b.mxid,b.mc FROM @a a
INNER JOIN @b b
ON a.fid=b.fid
/*
1 001 1 1 0011
1 1 2 0012
1 1 3 001x
2 002 2 4 002z
2 2 5 002n
*/
DECLARE @a TABLE(fid INT,mc VARCHAR(200))
DECLARE @b TABLE(fid INT,mxid INT,mc VARCHAR(200))INSERT @a SELECT 1,'001'
UNION ALL SELECT 2,'002'INSERT @b SELECT 1,1,'0001'
UNION ALL SELECT 1,2,'00002'
UNION ALL SELECT 1,3,'000224'
UNION ALL SELECT 2,1,'00001'
UNION ALL SELECT 2,2,'002n'select
fid=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then a.fid else null end,
mc=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then a.mc else null end,
fid=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then b.mc else null end,
b.mxid,b.mc
from @a a right join @b b
on a.fid=b.fid
--上面有点错误:DECLARE @a TABLE(fid INT,mc VARCHAR(200))
DECLARE @b TABLE(fid INT,mxid INT,mc VARCHAR(200))INSERT @a SELECT 1,'001'
UNION ALL SELECT 2,'002'INSERT @b SELECT 1,1,'0001'
UNION ALL SELECT 1,2,'00002'
UNION ALL SELECT 1,3,'000224'
UNION ALL SELECT 2,1,'00001'
UNION ALL SELECT 2,2,'002n'select
fid=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then a.fid else null end,
mc=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then a.mc else null end,
fid=case when not exists(select 1 from @b where fid=b.fid and mxid<b.mxid) then b.fid else null end,
b.mxid,b.mc
from @a a right join @b b
on a.fid=b.fid
那你提供一点思路吧
我用cxgrid与数据集组件相连.但我不想用存储过程写.
DECLARE @b TABLE(fid INT,mxid INT,mc VARCHAR(200))INSERT @a SELECT 1,'001'
UNION ALL SELECT 2,'002'INSERT @b SELECT 1,1,'0001'
UNION ALL SELECT 1,2,'00002'
UNION ALL SELECT 1,3,'000224'
UNION ALL SELECT 2,1,'00001'
UNION ALL SELECT 2,2,'002n'select
case when mxid=(select min(mxid) from @b where fid=tp.afid) then afid else null end as afid,
case when mxid=(select min(mxid) from @b where fid=tp.afid) then amc else null end as amc,
case when mxid=(select min(mxid) from @b where fid=tp.afid) then fid else null end as amc,
mxid,mc
from (
select a.fid as afid,a.mc as amc,b.* from @a a left join @b b
on a.fid=b.fid)tpafid amc amc mxid mc
1 001 1 1 0001
NULL NULL NULL 2 00002
NULL NULL NULL 3 000224
2 002 2 1 00001
NULL NULL NULL 2 002n
create table A(ih int,hc varchar(10),date datetime)
insert into A values(1, 'Q1', '2007-1-1')
insert into A values(1, 'Q1', '2007-2-1')
insert into A values(2, 'Q2', '2007-1-5')
insert into A values(3, 'Q3', '2007-3-1')
create table B(ih int,idx int,jg decimal(18,1))
insert into B values(1, 1, 10.2)
insert into B values(1, 2, 10.3)
insert into B values(1, 3, 9.8 )
insert into B values(2, 1, 11 )
insert into B values(2, 2, 10.2)
insert into B values(3, 2, 12 )
goselect
ih = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m1
where m4.ih=m1.ih) then cast(ih as varchar) else '' end ,
hc = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m2
where m4.ih=m2.ih) then hc else '' end ,
date = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m3
where m4.ih=m3.ih) then convert(varchar(10),date,120) else '' end ,
jg
from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m4drop table A,B/*
ih hc date jg
------------------------------ ---------- ---------- --------------------
1 Q1 2007-01-01 10.2
10.3
9.8
2 Q2 2007-01-05 11.0
10.2
3 Q3 2007-03-01 12.0(所影响的行数为 6 行)
*/
insert into A values(1 , '001')
insert into A values(2 , '002')
create table B(fid int , mxid int , mc varchar(20))
insert into B values(1 , 1 , '0001')
insert into B values(1 , 2 , '00002')
insert into B values(1 , 3 , '000224')
insert into B values(2 , 1 , '00001')
insert into B values(2 , 2 , '0254')
goselect Afid = case when mxid = (select min(mxid) from
(
select a.fid Afid,a.mc Amc , b.fid Bfid , b.mxid , b.mc Bmc from a , b where a.fid = b.fid
) m1 where Afid = m.Afid) then cast(Afid as varchar) else '' end ,
Amc = case when mxid = (select min(mxid) from
(
select a.fid Afid,a.mc Amc , b.fid Bfid , b.mxid , b.mc Bmc from a , b where a.fid = b.fid
) m1 where Afid = m.Afid) then Amc else '' end ,
Bfid = case when mxid = (select min(mxid) from
(
select a.fid Afid,a.mc Amc , b.fid Bfid , b.mxid , b.mc Bmc from a , b where a.fid = b.fid
) m1 where Afid = m.Afid) then cast(Bfid as varchar) else '' end ,
mxid , Bmc
from
(
select a.fid Afid,a.mc Amc , b.fid Bfid , b.mxid , b.mc Bmc from a , b where a.fid = b.fid
) mdrop table A,B/*
Afid Amc Bfid mxid Bmc
------------------------------ -------------------- ------------------------------ ----------- --------------------
1 001 1 1 0001
2 00002
3 000224
2 002 2 1 00001
2 0254(所影响的行数为 5 行)
*/
比如PB中,就有相应属性设置就OK