select * from tb2 tem where dt=(select max(dt) from tb2 where id=tem.id)
解决方案 »
- datetime如何转换为bigint类型?
- 做了发布和订阅,但是在订阅数据库表里插入数据会弹出“违反了 PRIMARY KEY 约束”
- sql2008使用中感觉不稳定
- 一个非常简单的问题,希望大家帮忙,关于数据库备份的命令,知道的朋友请进!!!
- 存储过程中生成临时表问题,希望各位大侠帮忙,在线等,解决后马上结帖
- 我不需要把SQL Server 2005上的数据同步到SQL Server 2008上,需要怎么做?谢谢!
- 请高手指教如何查询系统表select [name] from openrowset('sqloledb'。。。
- exists子查询的问题,请指教
- 无法还原数据库,总提示磁盘空间不足!请大家一定要帮帮忙!
- 在线等待,小问题
- 对每月应缴金额作出统计(自觉难度很高),求教!
- 计算某列的字符串相加,求一个sql语句(100分)
select * from tb2 tem where dt=(select max(dt) from tb2 where id=tem.id)) b where a.id=b.id
Select * from tb2 a where dt = (select max(dt) from tb2 where id=a.id))
select M.*,B.Name from (Tb2 as M
Inner Join (select max(DT) as X,ID as Y from Tb2 group by ID) as A ON M.ID=A.Y And M.DT=A.X)
Left Join Tb1 as B On M.id=B.id
[ID] CHAR(10)PRIMARY KEY NOT NULL,
[Name] CHAR(40)NOT NULL)CREATE TABLE tb2(
[ID] CHAR(10) NOT NULL,
[DT] DATETIME NOT NULL,
[VAL] CHAR(40) NOT NULL)
GOALTER TABLE tb2 ADD CONSTRAINT [FK_tb2_tb1] FOREIGN KEY
(
[ID]
)REFERENCES tb1
(
[ID]
)
GOINSERT INTO tb1
VALUES ('001','股票1')INSERT INTO tb1
VALUES('002','股票2')INSERT INTO tb1
VALUES('003','股票3')GO
INSERT INTO tb2
VALUES ('001','20031010','10.00')INSERT INTO tb2
VALUES('001','20031015','18.00')INSERT INTO tb2
VALUES('002','20031010','4.00')INSERT INTO tb2
VALUES('002','20031015','8.00')INSERT INTO tb2
VALUES('003','20031009','20.00')INSERT INTO tb2
VALUES('003','20031015','21.00')
GOSELECT * FROM TB1 --查询结果如下:ID Name
---------- ------------------------------------
001 股票1
002 股票2
003 股票3 SELECT * FROM TB2 --查询结果如下:ID DT VAL
---------- -------------------------- ---------
001 2003-10-10 00:00:00.000 10.00
001 2003-10-12 00:00:00.000 18.00
002 2003-10-10 00:00:00.000 4.00
002 2003-10-13 00:00:00.000 8.00
003 2003-10-09 00:00:00.000 20.00
003 2003-10-10 00:00:00.000 21.00 --取所有股票的最后一次成交时间和成交价的查询语句如下:SELECT [ID],[Name],[DT],[VAL] FROM(
SELECT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT] IN(SELECT MAX(DT)FROM TB2 GROUP BY [ID])
)AS YOURWANTID Name DT VAL
---------- -------------------------------- ------
001 股票1 2003-10-15 00:00:00.000 18.00
002 股票2 2003-10-15 00:00:00.000 8.00
003 股票3 2003-10-15 00:00:00.000 21.00(所影响的行数为 3 行)
SELECT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT] IN(SELECT MAX(DT)FROM TB2 GROUP BY [ID])
)AS YOURWANT
上面的返回集只包含了有成交价的股票,但我希望
如果股票表tb1中某只股票在成交表tb2没有成交记录,返回的记录集中必须包含该股票记录,但最后一次成交时间为空
--添加测试数据:
INSERT TB1 SELECT '004','股票4'INSERT TB2([ID]) SELECT '004' --该股票在成交表tb2没有成交记录GO--有成交价的记录SELECT DISTINCT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT]IN(SELECT MAX(DT)FROM TB2 GROUP BY[ID]) --没有成交价的记录SELECT DISTINCT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT]IS NULLGO--最终查询语句为:SELECT DISTINCT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT]IN(SELECT MAX(DT)FROM TB2 GROUP BY[ID])
UNION
SELECT DISTINCT A.[ID],[Name],[VAL],[DT]
FROM TB1 A,TB2 B
WHERE A.[ID]=B.[ID] AND [DT]IS NULLID Name VAL DT
---- ------- ------ -------------
001 股票1 18.00 2003-10-15 00:00:00.000
002 股票2 8.00 2003-10-15 00:00:00.000
003 股票3 21.00 2003-10-15 00:00:00.000
004 股票4 NULL NULL(所影响的行数为 4 行)