请教各各位帮我看看象这样的问题怎么写SQL语句 ( 积分: 100 )

有"一个"送奶工的日志表(num) a1,a2,a3,a4,a5,a6,a7,a8,a9,a10是奶品 现有六天的记录送奶的
记录,但是有些天奶工有的奶品不送就为空或者为0 现在我要将这6天的送奶记录筛选打印,
只要打印有记录的奶品一瓶不送的奶品就不打印了

天 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
1 0 1 0 5 8
2 0 4
3 0 5 4
4 0
5 0 8 4
6 0 8 5

我用select sum(a1) as a1 ,sum(a2) as a2 ,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5,sum(a6) as a6 from num
返回的记录是
a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
0 9 0 15 0 16 4 0 4 4
其中 有写列的和是为0 的 我不需要那些列

想写一条语句直接返回那些不为0 的列中的所有记录就是

天 a2 a4 a6 a7 a9 a10
1 1 5 8
2 4
3 5 4
4
5 8 4
6 8 5

我想法用select (xxx) from num
这里的xxx 就是动态的字段

有"一个"送奶工的日志表(num) a1,a2,a3,a4,a5,a6,a7,a8,a9,a10是奶品 现有六天的记录送奶的
记录,但是有些天奶工有的奶品不送就为空或者为0 现在我要将这6天的送奶记录筛选打印,
只要打印有记录的奶品一瓶不送的奶品就不打印了

天 a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
1 0 1 0 5 8
2 0 4
3 0 5 4
4 0
5 0 8 4
6 0 8 5

我用select sum(a1) as a1 ,sum(a2) as a2 ,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5,sum(a6) as a6 from num
返回的记录是
a1 a2 a3 a4 a5 a6 a7 a8 a9 a10
0 9 0 15 0 16 4 0 4 4
其中 有写列的和是为0 的 我不需要那些列

想写一条语句直接返回那些不为0 的列中的所有记录就是

天 a2 a4 a6 a7 a9 a10
1 1 5 8
2 4
3 5 4
4
5 8 4
6 8 5

我想法用select (xxx) from num
这里的xxx 就是动态的字段

在前端应用程序编程实现或者通过存储过程实现

用字符輸出行不行？條件為＞0的就改寫，其它的為空。（這麼想的）

create procedure pLog
as
begin
declare
@Day int,
@name varchar(10),
@SQL NVarchar(4000)

if exists(select 1 from sysobjects where name='tmpLog')
drop table tmpLog
declare cur cursor LOCAL for select 天 from tLog
open cur
fetch next from cur into @Day
while @@fetch_status=0
begin
declare tempcur cursor LOCAL for select name from syscolumns where id=(select id from sysobjects where name='tLog') and name<>'天'
open tempcur
fetch next from tempcur into @name
while @@fetch_status=0
begin
set @SQL=N'if exists(select '+@name+N' from tLog where 天='+convert(varchar(10),@Day)+N' and '+@name+N'>0) begin if exists(select id from sysobjects where name=''tmpLog'') begin if not exists(select 1 from syscolumns where id=(select id from sysobjects where name=''tmpLog'') and name='''+@name+N''') alter table tmpLog add '+@name+N' int end else create table tmplog (天 int,'+@name+N' int) end'
exec(@SQL)

fetch next from tempcur into @name
end
close tempcur
deallocate tempcur

fetch next from cur into @Day
end
close cur
deallocate cur

set @SQL=''
declare cur cursor LOCAL for select name from syscolumns where id=(select id from sysobjects where name='tmpLog')
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
set @SQL=@SQL+@name+','
fetch next from cur into @name
end
close cur
deallocate cur
if @SQL<>''
begin
set @SQL=substring(@SQL,1,len(@SQL)-1)
set @SQL=N'insert into tmpLog select '+@SQL+N' from tLog'
exec(@SQL)
end
select * from tmpLog
if exists(select 1 from sysobjects where name='tmpLog')
drop table tmpLog
end
嗬嗬，看着挺简单的，竟然写了将近一个小时，你自己看看吧，俺要睡觉了，里面还可以在优化，比如用临时表，我嫌麻烦，没有用[]晚安

又不明白的地方，给我留言[]

有这么烦吗,这觉得这样挺好:
var OutText : string;
......
Query1.close;
Query1.SQL.text := 'SELECT * FROM 日志表(num) order by Id,date';
Query1.Open;
while not Query1.Eof do
OutText := OutText + #13 + Query1.FieldByName('a1').AsString + '|' + ... + Query1.FieldByName('a10').AsString;
Query1.Next;
begin
end;

biili,:我还要查询返回记录的啊！
你能发表你的意见吗！

各位SQL的高手快继续发言啊！希望大家能帮我解决！

这样如何：
var
sStr:string;
sStr1,sStr2:string
begin

'''''''
sStr1 :='select 天'
sStr2 :=' from num order by 天'
sStr:='select sum(a1) as a1 ,sum(a2) as a2 ,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5,sum(a6) as a6 from num '
with query1 do
begin
close;
sql.clear;
sql.add(sStr);
prepare;
open;
if fieldbyname('a1').AsInteger <> 0 then
sStr1 := sStr1 +',a1 '
if fieldbyname('a2').AsInteger <> 0 then
sStr1 := sStr1 +',a2 '
if fieldbyname('a3').AsInteger <> 0 then
sStr1 := sStr1 +',a3 '
if fieldbyname('a4').AsInteger <> 0 then
sStr1 := sStr1 +',a4 '
if fieldbyname('a5').AsInteger <> 0 then
sStr1 := sStr1 +',a5 '
if fieldbyname('a6').AsInteger <> 0 then
sStr1 := sStr1 +',a6 '
with query2 do
begin
close;
sql.clear;
sql.add(str1+str2);
prepare;
open;
end;
end;
end;

要是六天都没有送出一瓶奶，那怎么办，SQL查询语可不支持空列

哈哈，在中间再加条语句
if (fieldbyname('a1').AsInteger <> 0)
and (fieldbyname('a2').AsInteger <> 0)
and (fieldbyname('a3').AsInteger <> 0)
and (fieldbyname('a4').AsInteger <> 0)
and (fieldbyname('a5').AsInteger <> 0)
and (fieldbyname('a6').AsInteger <> 0)
then
………………；处理
如果只显示 天 则不需要添加这一段
如果想其他处理 则在省略号处添语句就可以了啊。

[red]Endy.Vee:能否请您就根据我的这个问题写些建议！[/red]

var
sStr: string;
sStr1, sStr2, sStr3: string;
intNum: Integer;
begin
sStr1 := 'select ';
sStr2 := ' from num';
sStr := 'select sum(a1) as a1 ,sum(a2) as a2 ,sum(a3) as a3,sum(a4) as a4,sum(a5) as a5,sum(a6) as a6 from num ';
with query1 do
begin
close;
sql.clear;
sql.add(sStr);
prepare;
open;
for intNum := 0 to Fields.Count - 1 do
begin
if Fields[intNum].AsInteger <> 0 then
sStr3 := sStr3 + Fields[intNum].FieldName + ',';
end;
if sStr3 <> '' then
begin
sStr3 := copy(trim(sStr3), 1, length(trim(sStr3)) - 1);
with query2 do
begin
close;
sql.clear;
sql.add(sstr1 + sStr3 + sstr2);
prepare;
open;
end;
end
else
ShowMessage('这段时间没有送奶！');
end;
end;
我把huaya1127兄的代码改造了一下，这样比用存储过程简单，当时我以为你要在SQL Server中实现[]，楼主可以看一下是否满足你的要求阿

或许这个问题太难！ 但希望大家能继续关注！

不是都有答案了吗，怎么还有关注，要不试试这个存储过程：
CREATE PROCEDURE test
AS
select * into #T from num
declare @i int,@sql varchar(100)
set @i=1
while @i<=10
begin
select @sql='if not exists(select * from num where isnull(a'+convert(varchar(2),@i)+',0)<>0) alter table #T drop column a'+convert(varchar(2),@i),
@i=@i+1
exec(@sql)
end
select * from #T
GO