比如
A表
id name age
1 aaa 12
2 bb 13
3 cc 11
B表
id time state A_id
1 2010 1 1
2 2011 0 1
3 2012 1 1
4 2011 1 2
5 2015 1 3
6 2015 0 2
7 2015 1 3
要的结果是
a.id b.id name time
1 3 aaa 2011
1 2 aaa 2012
2 2 bb 2015
3 7 cc 2015
3 6 cc 2015{@参数@}是一种传参数的方式要的效果 根据 tvehicle的VEHICLENO 获取最后的2条数据 (TVEHICLE_PACK。ENDDATE )如果可以,请各位参考我的代码改变,在这里说声谢谢啦!~~~IF('{@QueryType@}' = '0') --按档案查询
BEGIN
DECLARE @WHEREPARAN NVARCHAR(70) --WHERE 参数
-- 当 选择的是“车辆类别”,并且是“已过户”
IF('{@SearchField@}' ='OFTYPE' AND '{@SearchTxt@}' = '2398')
BEGIN
SELECT @WHEREPARAN = 'TV.OFTYPE =2398' END
ELSE -- 其他
BEGIN
SELECT @WHEREPARAN = CASE WHEN '{@SearchTxt@}' = '' THEN ' 1=1 AND TV.OFTYPE != 2398' --当输入条件为空状态
WHEN '{@SearchField@}' ='OFTYPE' THEN 'TV.OFTYPE = '+'{@SearchTxt@}' --当选择车辆类别,并且不是“已过户”
ELSE '{@SearchField@}'+'='+'''{@SearchTxt@}'''+' AND TV.OFTYPE != 2398' --其他
END --END CASE
END
EXEC(
'SELECT TV.VEHICLENO ''NO'',
TP.LISTNO,
TP.BEGINDATE ,
TP.ENDDATE,
(SELECT CTITLE FROM T4PL WHERE DEPTNAME = TP.OFCOMPANY ) ''CTITLE'',
(SELECT VALUE AS DISPLAY FROM TDICTIONARY WHERE PID=1855 AND ID = TP.SMODULE) ''SMODULE'',
( SELECT CNAME AS DISPLAY FROM TCUSTOMER WHERE TCUSTOMER.ID =TP.BALANCEUNIT) ''BALANCEUNIT'',
TP.REMARK
FROM TVEHICLE_PACK AS TP
LEFT JOIN TVEHICLE AS TV
ON TV.ID = TP.LISTID
WHERE '+@WHEREPARAN
);
END
ELSE --按合同时间查询
BEGIN
SELECT TV.VEHICLENO 'NO',
TP.LISTNO,
TP.BEGINDATE,
TP.ENDDATE,
(SELECT CTITLE FROM T4PL WHERE DEPTNAME = TP.OFCOMPANY ) 'CTITLE',
(SELECT VALUE AS DISPLAY FROM TDICTIONARY WHERE PID=1855 AND ID = TP.SMODULE) 'SMODULE',
( SELECT CNAME AS DISPLAY FROM TCUSTOMER WHERE TCUSTOMER.ID =TP.BALANCEUNIT) 'BALANCEUNIT',
TP.REMARK
FROM
TVEHICLE_PACK AS TP
LEFT JOIN TVEHICLE AS TV
ON TV.ID = TP.LISTID
WHERE
CONVERT(DATETIME,'{@StartDate@}') BETWEEN BEGINDATE AND ENDDATE
AND CONVERT(DATETIME,'{@EndDate@}') BETWEEN BEGINDATE AND ENDDATE
AND TV.OFTYPE!= 2398 END
A表
id name age
1 aaa 12
2 bb 13
3 cc 11
B表
id time state A_id
1 2010 1 1
2 2011 0 1
3 2012 1 1
4 2011 1 2
5 2015 1 3
6 2015 0 2
7 2015 1 3
要的结果是
a.id b.id name time
1 3 aaa 2011
1 2 aaa 2012
2 2 bb 2015
3 7 cc 2015
3 6 cc 2015{@参数@}是一种传参数的方式要的效果 根据 tvehicle的VEHICLENO 获取最后的2条数据 (TVEHICLE_PACK。ENDDATE )如果可以,请各位参考我的代码改变,在这里说声谢谢啦!~~~IF('{@QueryType@}' = '0') --按档案查询
BEGIN
DECLARE @WHEREPARAN NVARCHAR(70) --WHERE 参数
-- 当 选择的是“车辆类别”,并且是“已过户”
IF('{@SearchField@}' ='OFTYPE' AND '{@SearchTxt@}' = '2398')
BEGIN
SELECT @WHEREPARAN = 'TV.OFTYPE =2398' END
ELSE -- 其他
BEGIN
SELECT @WHEREPARAN = CASE WHEN '{@SearchTxt@}' = '' THEN ' 1=1 AND TV.OFTYPE != 2398' --当输入条件为空状态
WHEN '{@SearchField@}' ='OFTYPE' THEN 'TV.OFTYPE = '+'{@SearchTxt@}' --当选择车辆类别,并且不是“已过户”
ELSE '{@SearchField@}'+'='+'''{@SearchTxt@}'''+' AND TV.OFTYPE != 2398' --其他
END --END CASE
END
EXEC(
'SELECT TV.VEHICLENO ''NO'',
TP.LISTNO,
TP.BEGINDATE ,
TP.ENDDATE,
(SELECT CTITLE FROM T4PL WHERE DEPTNAME = TP.OFCOMPANY ) ''CTITLE'',
(SELECT VALUE AS DISPLAY FROM TDICTIONARY WHERE PID=1855 AND ID = TP.SMODULE) ''SMODULE'',
( SELECT CNAME AS DISPLAY FROM TCUSTOMER WHERE TCUSTOMER.ID =TP.BALANCEUNIT) ''BALANCEUNIT'',
TP.REMARK
FROM TVEHICLE_PACK AS TP
LEFT JOIN TVEHICLE AS TV
ON TV.ID = TP.LISTID
WHERE '+@WHEREPARAN
);
END
ELSE --按合同时间查询
BEGIN
SELECT TV.VEHICLENO 'NO',
TP.LISTNO,
TP.BEGINDATE,
TP.ENDDATE,
(SELECT CTITLE FROM T4PL WHERE DEPTNAME = TP.OFCOMPANY ) 'CTITLE',
(SELECT VALUE AS DISPLAY FROM TDICTIONARY WHERE PID=1855 AND ID = TP.SMODULE) 'SMODULE',
( SELECT CNAME AS DISPLAY FROM TCUSTOMER WHERE TCUSTOMER.ID =TP.BALANCEUNIT) 'BALANCEUNIT',
TP.REMARK
FROM
TVEHICLE_PACK AS TP
LEFT JOIN TVEHICLE AS TV
ON TV.ID = TP.LISTID
WHERE
CONVERT(DATETIME,'{@StartDate@}') BETWEEN BEGINDATE AND ENDDATE
AND CONVERT(DATETIME,'{@EndDate@}') BETWEEN BEGINDATE AND ENDDATE
AND TV.OFTYPE!= 2398 END
解决方案 »
- 数据如何转置成指定的报表格式,谢谢
- 新手提问。1条SQL语句问题。请多多帮助!
- 数据库备份的SQL怎么写?我不想备份成.bak文件,而是备份成cyylsystem_Data和cyylsystem_log的sql怎么写
- Replace()???????????
- 如何用SQL语句给数据库添加用户?
- sql 批量执行 存储过程时
- sql理论上一个东西,有点疑惑
- 求关系表连接查询SQL语句
- SQL 管理权限问题请教
- How to send a e-mail to operator when the disk out of space?(wait online)
- 两张表的记录进行比较
- sql server 自定义函数试用??
6 2015 0 2a_id = 2有两条,为何结果只有一条?
from a , t where a.id = t.a_id and t.time in (select top 2 time from b where a_id = t.a_id order by time desc)--如果是变量,则为:
declare @top as int
set @top = 2
exec('select a.id [a.id] , t.id [b.id] , a.name , t.time
from a , t where a.id = t.a_id and t.time in (select top ' + ltrim(@top) + ' time from b where a_id = t.a_id order by time desc)')
insert into a values(1 , 'aaa', 12)
insert into a values(2 , 'bb' , 13)
insert into a values(3 , 'cc' , 11)
create table B(id int, time int, state int,A_id int)
insert into b values(1 , 2010 , 1 , 1)
insert into b values(2 , 2011 , 0 , 1)
insert into b values(3 , 2012 , 1 , 1)
insert into b values(4 , 2011 , 1 , 2)
insert into b values(5 , 2015 , 1 , 3)
insert into b values(6 , 2015 , 0 , 2)
insert into b values(7 , 2015 , 1 , 3)
goselect a.id [a.id] , t.id [b.id] , a.name , t.time
from a , b t where a.id = t.a_id and t.time in (select top 2 time from b where a_id = t.a_id order by time desc)
/*
a.id b.id name time
----------- ----------- ---------- -----------
1 2 aaa 2011
1 3 aaa 2012
2 4 bb 2011
2 6 bb 2015
3 7 cc 2015
3 5 cc 2015(所影响的行数为 6 行)
*/--如果是变量,则为:
declare @top as int
set @top = 2
declare @sql as varchar(1000)
set @sql = 'select a.id [a.id] , t.id [b.id] , a.name , t.time from a , b t where a.id = t.a_id and t.time in (select top ' + cast(@top as varchar) + ' time from b where a_id = t.a_id order by time desc)'
exec(@sql)
/*
a.id b.id name time
----------- ----------- ---------- -----------
1 2 aaa 2011
1 3 aaa 2012
2 4 bb 2011
2 6 bb 2015
3 7 cc 2015
3 5 cc 2015
*/drop table a , b
go
create table A(id int,name varchar(10),age int)
insert into A values(1 , 'aaa', 12)
insert into A values(2 , 'bb' , 13)
insert into A values(3 , 'cc' , 11)
go
if object_id('B') is not null drop table B
go
create table B
(
id int identity(1,1) primary key,
time int,
state int,
A_id int
)
insert into B select 2010,1,1
union all select 2011,0,1
union all select 2012,1,1
union all select 2011,1,2
union all select 2015,1,3
union all select 2015,0,2
union all select 2015,1,3
goselect A1.id [A1.id],B1.id,A1.name,B1.time
from A A1,B B1
where A1.id = B1.a_id and B1.time in
(select top 2 time from B where a_id = B1.a_id order by time desc)
A1.id id name time
----------- ----------- ---------- -----------
1 2 aaa 2011
1 3 aaa 2012
2 4 bb 2011
3 5 cc 2015
2 6 bb 2015
3 7 cc 2015(6 行受影响)
a.id b.id name time
1 3 aaa 2011
1 2 aaa 2012
2 2 bb 2015
3 7 cc 2015
3 6 cc 2015看不懂啊
INSERT A
SELECT 1 , 'aaa', 12 UNION ALL
SELECT 2 , 'bb' , 13 UNION ALL
SELECT 3 , 'cc' , 11CREATE TABLE B
(
id int identity(1,1) primary key,
time int,
state int,
A_id int
)
INSERT INTO B
SELECT 2010,1,1 UNION ALL
SELECT 2011,0,1 UNION ALL
SELECT 2012,1,1 UNION ALL
SELECT 2011,1,2 UNION ALL
SELECT 2015,1,3 UNION ALL
SELECT 2015,0,2 UNION ALL
SELECT 2015,1,3
select A_id,C.id B_id,name,time
from A join
(
select *,rnt=ROW_NUMBER()
over(partition by A_id order by time)
from B
) C
on A.id=C.A_id
where C.rnt<3/*
A_id B_id name time
----------- ----------- ---------- -----------
1 1 aaa 2010
1 2 aaa 2011
2 4 bb 2011
2 6 bb 2015
3 7 cc 2015
3 5 cc 2015(6 行受影响)*/