表一:TrainType表
ID TypeName Memo
1 一吨 一吨一下含一吨
2 二吨
3 三吨
4 大车表二:Price表
Start Arrive TrainTypeID price
天津 北京 1 500.00
天津 北京 2 1000.00
天津 北京 4 2000.00
天津 唐山 1 700.00查询时给定条件是Start=天津、Arrive=北京,要求输出格式为下表:
Start Arrive 一吨 二吨 三吨 大车
天津 北京 500.00 1000.00 2000.00
用sql实现上述的内容,多谢高手指点!
ID TypeName Memo
1 一吨 一吨一下含一吨
2 二吨
3 三吨
4 大车表二:Price表
Start Arrive TrainTypeID price
天津 北京 1 500.00
天津 北京 2 1000.00
天津 北京 4 2000.00
天津 唐山 1 700.00查询时给定条件是Start=天津、Arrive=北京,要求输出格式为下表:
Start Arrive 一吨 二吨 三吨 大车
天津 北京 500.00 1000.00 2000.00
用sql实现上述的内容,多谢高手指点!
解决方案 »
- 控件发布,数据关联输入拼音过滤控件
- 我用MEMO的LoadFromFile读取一个比较大的文本文件(大概5、6M吧)时程序会长时间停止响应,请问如何才能加个进度条?
- 关于SQL语句单引号的问题。
- 调用OpenPrinter需要引用什么单元?
- 改版后丢失的未解之迷:~~ 获取打印机的状态并控制打印机,问题解决必高分相送!!!
- 调度算法问题~!!!!!!!!!!
- 如何自定义一个函数?
- MRPII生产制造设计
- 两个问题 分不多了在线等。。
- TO cutelocust,倒分很简单;下边问题回答了,分就是你的了
- 请教如何设置一个控件的上一级控件
- ★建议论坛增加加亮楼主回复的帖子的功能!同意的请举手,谢谢!
sum(case TrainTypeID when 1 then price else then 0 end ) as 一吨,
sum(case TrainTypeID when 2 then price else then 0 end ) as 二吨,
sum(case TrainTypeID when 3 then price else then 0 end ) as 三吨,
sum(case TrainTypeID when 4 then price else then 0 end ) as 大车
from price
where Start = '天津' and Arrive = '北京'
group by Start,Arrive
sum(case TrainTypeID when 1 then price else then 0 end ) as 一吨,
sum(case TrainTypeID when 2 then price else then 0 end ) as 二吨,
sum(case TrainTypeID when 3 then price else then 0 end ) as 三吨,
sum(case TrainTypeID when 4 then price else then 0 end ) as 大车
from price
where Start = '天津' and Arrive = '北京'
group by Start,Arrive
select Price.Start,Price.Arrive,Price.Price,TrainType.TypeName from Price,TrainType Where TrainType.id=Price.TrainTypeID
godeclare @sql varchar(8000)
set @sql = 'select start as 发车站,arrive as 终点站'
select @sql = @sql + ',(case TypeName when '''+TypeName+''' then Price else 0 end)
['+TypeName+']'
from (select distinct TypeName from Price_View where (Start='天津') and (Arrive="北京") ) as a from Price_view
exec(@sql)
TrainType表的记录要是固定的,那就用firetoucher(风焱)的,如果是不固定的,就要考虑我的这种情况了!
我的TrainType表的记录是不固定的,可是用你的这种情况,我不知道该怎么继续下去!能否教教小妹!
declare @sql varchar(8000)
set @sql = 'select Start,Arrive,'
select @sql = @sql + 'sum(case TrainTypeID when '''+[ID]+'''
then price else 0 end) as '''+TypeName+''','
from (select distinct * from TrainType) as a
select @sql = left(@sql,len(@sql)-1) + ' from price group by Start,Arrive'
exec(@sql)
go
declare @sql varchar(8000)
set @sql = 'select Start,Arrive,'
select @sql = @sql + 'sum(case TrainTypeID when '''+[ID]+'''
then price else 0 end) as '''+TypeName+''','
from (select distinct * from TrainType) as a
select @sql = left(@sql,len(@sql)-1) + ' from price group by Start,Arrive'
exec(@sql)
go
declare @sql varchar(8000)
set @sql = 'select Start,Arrive,'
select @sql = @sql + 'sum(case TrainTypeID when '''+[ID]+'''
then price else 0 end) as '''+TypeName+''','
from (select distinct * from TrainType) as a
select @sql = left(@sql,len(@sql)-1) + ' from price group by Start,Arrive'
exec(@sql)
go
str_where:='where Start = ''天津'' and Arrive = ''北京'' group by Start,Arrive';
str_sum:='';
for i:=0 to table_traintype.recordcount do
str_sum:=str_sum+'sum(case TrainTypeID when'+table_traintype.fieldbyname('id').asstring+' then price else then 0 end ) as'+table_traintype.fieldbyname('typename').asstring+',';
str_sum:=leftstr(str_sum,length(str_sum)-1);//去掉最后一个逗号:, 用到strutils单元
str_sql:=str_select+str_sum+str_from;
//应该可以了吧
with query1 do
begin
close;
sql.clear;
sql.add('select * from TrainType');
open;
first;
end;
with query2 do
begin
close;
sql.clear;
s:='select Start,Arrive,';
while nor query1.eof do
begin
s:= s+'sum(case TrainTypeID when '''+query1.fieldsbyname('ID').asstring+'''
then price else 0 end) as '''+query1.fieldsbyname(TypeName).asstring+''',';
query1.next;
end;
s:=left(s,length(s)-1) + ' from price group by Start,Arrive'
sql.add(s);
open;
end;
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='一吨') as '一吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='二吨') as '二吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='三吨') as '三吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='大车') as '大车'
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='一吨') as '一吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='二吨') as '二吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='三吨') as '三吨',
(select sum(price) from price,TrainType where TrainType.ID=price.TrainTypeID and Start='天津' and Arrive='北京' and TypeName='大车') as '大车'
@bID int, --开始地ID(<=0 表示不作为查询条件)
@eID int, --到达地ID(同上)
@tID int --车型ID(<=0 表示查询全部车型)
)
as
declare @Sql varchar(3000) --拼Sql语句
declare @vID int --临时保存车型ID
declare @vType varchar(40) --临时保存车型名称
declare curGetTrain cursor
for select ID,Type from TrainType --取全部车型的游标
begin
set @Sql = 'Select distinct c.Name,d.Name ' --选择开始地和到达地
if @tID > -1 --如果查询指定ID的车型
begin
--得到要查询的车型ID和名称
select @vID = ID,@vType = Type from TrainType where ID = @tID
--如果不存在就直接退出
if @vID is null
return -1
--拼出查询此车型价格的子查询作为整个查询的一列
set @Sql = @Sql + ',(select price from AroundPrice p'
+' where p.BeginAroundAreaID = a.BeginAroundAreaID and '
+'p.EndAroundAreaID = a.EndAroundAreaID and '
+'p.TrainTypeId = '+cast(@vID as varchar(5))+') as '''+@vType+''''
end
else --查询全部车型
begin
--打开全部车型的游标
open curGetTrain
fetch curGetTrain into @vID , @vType
--拼出查询每个车型价格的子查询分别作为整个查询的一列
while (@@fetch_status = 0)
begin
set @Sql = @Sql + ',(select price from AroundPrice p'
+' where p.BeginAroundAreaID = a.BeginAroundAreaID and '
+'p.EndAroundAreaID = a.EndAroundAreaID and '
+'p.TrainTypeId = '+cast(@vID as varchar(5))+') as '''+@vType+''''
fetch curGetTrain into @vID ,@vType
end
close curGetTrain
end
deallocate curGetTrain
--添加Where子句
set @Sql = @Sql +' from AroundPrice a,AroundArea c,AroundArea d where a.BeginAroundAreaId = c.ID and a.endAroundAreaId = d.ID'
--如果开始地作为查询条件
if @bID > -1
begin
set @Sql = @Sql + ' and a.BeginAroundAreaId ='+Cast(@bID as varchar(5))
end
--如果到达地作为查询条件
if @eID > -1
begin
set @Sql = @Sql + ' and a.endAroundAreaId ='+Cast(@eID as varchar(5))
end
exec(@Sql)
if @@error = 0
return 0
else
return -1
end