原则
从最近得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 shuzhi
617 117.25 32
918 234.25 57
320 55.5 18
321 55.8 22
322 null 19
注null 代表没有数据 发现只敲空格就串行 不好看清楚
从最近得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 shuzhi
617 117.25 32
918 234.25 57
320 55.5 18
321 55.8 22
322 null 19
注null 代表没有数据 发现只敲空格就串行 不好看清楚
解决方案 »
- 求助 mysql 查询的一个问题 急需
- shell 操作mysql
- 谁知道 mysql工具 show profiles 显示的duration的时间的单位是什么,秒,毫秒,微秒,纳秒,皮秒,飞秒
- "ACMAIN_CHM"在吗?给我屡屡思路吧~~
- 关于视图创建mysql5.1 555555555555
- 谁来帮帮我啊,我是新手
- mysql5.0安全性如何?据说root密码可以reset?!同步和复制功能如何,支不支持双机热备份及同步?谢谢!来者有分
- 建表失败,原因是有个字段名为 describe 该怎么办?
- 版主,我要跳楼了,难道你没一点同情心。
- 请问哪儿有数据库的入门级学习资料下载?
- mysql得到月日和一个触发器问题!
- 关于mysql插入“—”这个符号后,就报错的问题。[
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)
)将就用下吧
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)
)/*usrcode usrguanid time
----------- --------- -----------
617 117.25 32
918 234.25 57
321 32.50 22
320 55.50 18
322 NULL 19(所影响的行数为 5 行)
*/
'截至2010-05-18 11:09:25 用户结帖率78.47% 总发帖:209 正常结帖:134 未结帖:45 当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html