有两个表:
Table A: Table B:
ID Name class_ID Name_id content
-------------- ----------------------------
1 john 1 1 math
2 hugo 2 1 english
3 mary 3 1 physics
4 kold 4 2 english
5 2 chinese
6 3 french 希望得到的结果
ID Name content
-------------------------------
1 john math
english
physics
2 hugo english
chinese
3 mary french
4 kold
请问各位大哥怎样能实现呢, 请指教
Table A: Table B:
ID Name class_ID Name_id content
-------------- ----------------------------
1 john 1 1 math
2 hugo 2 1 english
3 mary 3 1 physics
4 kold 4 2 english
5 2 chinese
6 3 french 希望得到的结果
ID Name content
-------------------------------
1 john math
english
physics
2 hugo english
chinese
3 mary french
4 kold
请问各位大哥怎样能实现呢, 请指教
解决方案 »
- 关于UNION
- vc连接远程sql server2000数据库问题
- 按月统计,按天分组汇总显示,SQL统计问题请教谢谢!
- 我想用update 更新下列SQL语句中QTY为1,该怎么写呢?
- SQL SERVER 2000占用大量内存会不会导致应用程序出错?
- 怪事!!!导VFP文件
- ASP+SQL查询查询问题--SQL占用很大内存???
- ~~~~~~~~~请大家帮帮小弟,过来看看,马上给分~~~~~~~~
- 谁想拉 "Microsoft SQL Server 2000 简体中文版 4 In 1",请进! (内详)
- ado对vfp的连接访问方式有几个!是不是就要通过ODBC!能不能直接访问那?
- 名称转id
- excel导入sqlserver可以选字段吗?
if object_id('tempdb.dbo.#a') is not null drop table #a
create table #a (ID int,Name varchar(11))
insert into #a
select 1,'john' union all
select 2,'hugo' union all
select 3,'mary' union all
select 4,'kold'
--> 测试数据: #b
if object_id('tempdb.dbo.#b') is not null drop table #b
create table #b (class_ID int,Name_id int,content varchar(11))
insert into #b
select 1,1,'math' union all
select 2,1,'english' union all
select 3,1,'physics' union all
select 4,2,'english' union all
select 5,2,'chinese' union all
select 6,3,'french';with T as
(
select gid=row_number()over(partition by a.id order by a.id),* from #a a left join #b b on a.id=b.name_id
)
select
ID=case when gid=1 then ltrim(ID) else '' end,
Name=case when gid=1 then Name else '' end,
content=isnull(content,'')
from T/*
ID Name content
------------ ----------- -----------
1 john math
english
physics
2 hugo english
chinese
3 mary french
4 kold
*/
ID Name content
-------------------------------
1 john math
english
physics
math
english
physics
math
english
physics
2 hugo english
chinese
english
chinese
3 mary french
4 kold
也就是说如果john有3门课程, 那么报表里就会将这3门课程重复3次,请问dawugui有遇到类似情况吗?
insert into a
select 1,'john' union all
select 2,'hugo' union all
select 3,'mary' union all
select 4,'kold'
create table b (class_ID int,Name_id int,content varchar(11))
insert into b
select 1,1,'math' union all
select 2,1,'english' union all
select 3,1,'physics' union all
select 4,2,'english' union all
select 5,2,'chinese' union all
select 6,3,'french';select a.id , a.name , b.content from a left join b on a.id = b.name_id
drop table a,b/*
id name content
----------- ----------- -----------
1 john math
1 john english
1 john physics
2 hugo english
2 hugo chinese
3 mary french
4 kold NULL(所影响的行数为 7 行)*/
insert into a
select 1,'john' union all
select 2,'hugo' union all
select 3,'mary' union all
select 4,'kold'
create table b (class_ID int,Name_id int,content varchar(11))
insert into b
select 1,1,'math' union all
select 2,1,'english' union all
select 3,1,'physics' union all
select 4,2,'english' union all
select 5,2,'chinese' union all
select 6,3,'french';select case when class_ID=(select min(class_ID) from b where name_id=a.id) then cast(a.id as varchar) else '' end as id,
case when class_ID=(select min(class_ID) from b where name_id=a.id) then a.name else '' end as name,
b.content
from a , b where a.id = b.name_id
union all
select cast(a.id as varchar) id, a.name , '' content from a where id not in (select name_id from B)drop table a,b/*
id name content
------------------------------ ----------- -----------
1 john math
english
physics
2 hugo english
chinese
3 mary french
4 kold (所影响的行数为 7 行)*/
id name content
------------------------------ ----------- -----------
1 john math
english
physics
2 hugo english
chinese
3 mary french
4 kold
首先在crystal report 连接好数据源,设计好报表的样式。 然后通过VB传递SQL查询语句到crystal report, 得到实时的报表。
我按照两位大侠的语句得出的结果总是重复
希望各位大侠继续指点,谢谢
name=case when isnull(class_ID,-1)=(select isnull(min(class_ID),-1)from b where Name_ID=a.id) then name else '' end,
isnull(content,'')content from A
left join B on a.ID=b.Name_ID
题目:
希望通过vb,查询记录集后, 把该SQL语句传递到crystal report, 生成报表。其中涉及到需要向两个表(多个表同样道理)得到数据。问题:
虽然用上面几位大侠提供的代码的确而且只能在SQL查询分析器里得到结果, 但不能在vb--crystal report这一过程中实现。也就是说在crystal report里依然出现重复记录集的结果。解决方法:
需要用子报表办法来解决。首先在crystal report里设计好主表(Table a),这里不需要任何的字段过滤(不需要与其他表有连接和条件语句),然后在主表(Table a)里插入子报表(Table b),同样不需要任何字段过滤。然后对子报表里需要显示的字段进行关联, 也就是例子里的把Table b的content关联到table a的ID。最好关键一步是在vb里,只需写一条查询table a 的SQL语句传递到crystal report.例如: select * from table a where id='****'.绝对不用写table b的任何东西。crystal report自动会根据子报表与主报表的关联关系显示table b的内容
谢谢上面几位大侠们的帮助。希望大家可以从中得到启示。
select case when class_ID=(select min(class_ID) from b where name_id=c.name_id) then cast(a.id as varchar) else '' end as id,
case when class_ID=(select min(class_ID) from b where name_id=c.name_id) then a.name else '' end as name,
b.content
from a , b as c where a.id = c.name_id
union all
select cast(a.id as varchar) id, a.name , '' content from a where id not in (select name_id from B)