刚才问题有点乱,重新发一下求sql语句

[wu_lin_326]

原则
从最近得shuzhi中插入数据(相等更好);
更新有数据的优先插入

view aa
usrcode shuzhi
617 32
918 57
320 18
321 22
322 19


table bb
usrcode usrguanid usrshuzhi
617 117.25 32
918 234.25 55
321 32.5 21
321 55.8 23
321 null 22
918 233.25 65
320 null 17
320 55.5 11
322 null 30

解释一下规则
321这条,bb表usrshuzhi中有21,22,23虽然22和aa表中相等,但没有数据,去掉,21和23与22比较距离都为1,但取+1;(23那条更新)
918这条,bb表中有55,65和aa表比较,55和57近,取55那条,更新
320这条,bb表中有17,11,虽然17近,但数组空,只能取11那条更新


通过上两个表我就得到视图cc
usrcode usrguanid time
617 117.25 32
918 234.25 57
320 55.5 18
321 55.8 22
322 null 19


注null 代表没有数据 发现只敲空格就串行 不好看清楚

[dawugui]

create table aa(usrcode int,shuzhi int)
insert into aa values(617 ,32)   
insert into aa values(918 ,57)   
insert into aa values(320 ,18)
insert into aa values(321 ,22)
insert into aa values(322 ,19)
create table bb(usrcode int,usrguanid decimal(18,2),usrshuzhi int)
insert into bb values(617, 117.25 ,32)
insert into bb values(918, 234.25 ,55)   
insert into bb values(321, 32.5   ,21)
insert into bb values(321, 55.8   ,23)
insert into bb values(321, null   ,22)
insert into bb values(918, 233.25 ,65)
insert into bb values(320, null   ,17)
insert into bb values(320, 55.5   ,11)
insert into bb values(322, null   ,30)  
go

select m.* ,
       usrguanid = (select top 1 usrguanid from bb n where m.usrcode = n.usrcode and n.usrguanid is not null order by abs(m.shuzhi - n.usrshuzhi) , n.usrshuzhi desc)
from aa m

drop table aa , bb

/*
usrcode     shuzhi      usrguanid            
----------- ----------- -------------------- 
617         32          117.25
918         57          234.25
320         18          55.50
321         22          55.80
322         19          NULL

（所影响的行数为 5 行）
*/

[永生天地]

帮顶，研究了一下，真的是很麻烦

[htl258_Tony]

[Quote=引用楼主 wu_lin_326 的回复:]
原则
从最近得shuzhi中插入数据(相等更好);
更新有数据的优先插入

view aa
usrcode shuzhi
617 32
918 57
320 18
321 22
322 19


table bb
usrcode usrguanid usrshuzhi
617 117.25 32
918……
[/Quote]刚才回的有问题吗

[htl258_Tony]

--> 生成测试数据表: [aa]
IF OBJECT_ID('[aa]') IS NOT NULL
    DROP TABLE [aa]
GO
CREATE TABLE [aa] ([usrcode] [int],[shuzhi] [int])
INSERT INTO [aa]
SELECT '617','32' UNION ALL
SELECT '918','57' UNION ALL
SELECT '320','18' UNION ALL
SELECT '321','22' UNION ALL
SELECT '322','19'

--> 生成测试数据表: [bb]
IF OBJECT_ID('[bb]') IS NOT NULL
    DROP TABLE [bb]
GO
CREATE TABLE [bb] ([usrcode] [int],[usrguanid] [numeric](5,2),[usrshuzhi] [int])
INSERT INTO [bb]
SELECT '617','117.25','32' UNION ALL
SELECT '918','234.25','55' UNION ALL
SELECT '321','32.5','21' UNION ALL
SELECT '321','55.8','23' UNION ALL
SELECT '321',NULL,'22' UNION ALL
SELECT '918','233.25','65' UNION ALL
SELECT '320',NULL,'17' UNION ALL
SELECT '320','55.5','11' UNION ALL
SELECT '322',NULL,'30'


-->SQL查询如下:
SELECT usrcode,
    usrguanid=(SELECT top 1 [usrguanid]  FROM [bb] WHERE [usrcode]=t.[usrcode] AND [usrguanid] IS NOT NULL ORDER BY ABS([usrshuzhi]-T.[shuzhi])),
    shuzhi
FROM [aa] t
/*
usrcode     usrguanid                               shuzhi
----------- --------------------------------------- -----------
617         117.25                                  32
918         234.25                                  57
320         55.50                                   18
321         55.80                                   22
322         NULL                                    19

(5 行受影响)
*/

这是原来回过的，有什么问题直说，再发一百贴也是一样。

[永生天地]

--> 生成测试数据表: [aa]
IF OBJECT_ID('[aa]') IS NOT NULL
    DROP TABLE [aa]
GO
CREATE TABLE [aa] ([usrcode] [int],[shuzhi] [int])
INSERT INTO [aa]
SELECT '617','32' UNION ALL
SELECT '918','57' UNION ALL
SELECT '320','18' UNION ALL
SELECT '321','22' UNION ALL
SELECT '322','19'

--> 生成测试数据表: [bb]
IF OBJECT_ID('[bb]') IS NOT NULL
    DROP TABLE [bb]
GO
CREATE TABLE [bb] ([usrcode] [int],[usrguanid] [numeric](5,2),[usrshuzhi] [int])
INSERT INTO [bb]
SELECT '617','117.25','32' UNION ALL
SELECT '918','234.25','55' UNION ALL
SELECT '321','32.5','21' UNION ALL
SELECT '321','55.8','23' UNION ALL
SELECT '321',NULL,'22' UNION ALL
SELECT '918','233.25','65' UNION ALL
SELECT '320',NULL,'17' UNION ALL
SELECT '320','55.5','11' UNION ALL
SELECT '322',NULL,'30'

select b.usrcode,b.usrguanid,a.shuzhi time 
from bb b left join aa a on  b.usrcode=a.usrcode
where (usrguanid is not null and
b.usrshuzhi = (select top 1 m.usrshuzhi  from bb m left join aa n on  m.usrcode=n.usrcode
where m.usrguanid is not null and b.usrcode=m.usrcode order by abs(b.usrshuzhi - a.shuzhi),b.usrshuzhi desc) 
)
or 
 (not exists(select 1 from bb where usrcode=b.usrcode and usrguanid is not null) 
)

将就用下吧