求sql语句.该如何解决

求sql语句...SQL codecreate table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10)

求sql语句...

SQL code

create table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10))INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第三个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第四个','2011-10-23','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-23','B班')goselect * from Table1 -----执行结果如下:第一个    2011-10-24 00:00:00.000    A班第一个    2011-10-24 00:00:00.000    B班第二个    2011-10-24 00:00:00.000    A班第二个    2011-10-24 00:00:00.000    B班第三个    2011-10-24 00:00:00.000    A班第五个    2011-10-24 00:00:00.000    A班第四个    2011-10-23 00:00:00.000    A班第五个    2011-10-23 00:00:00.000    B班

SQL code

--想要得到的结果如下：第一个    2011-10-24 00:00:00.000    A班第一个    2011-10-24 00:00:00.000    B班第二个    2011-10-24 00:00:00.000    A班第二个    2011-10-24 00:00:00.000    B班第三个    2011-10-24 00:00:00.000    A班第三个    2011-10-24 00:00:00.000    B班 --添加第五个    2011-10-24 00:00:00.000    A班第五个    2011-10-24 00:00:00.000    B班 --添加第四个    2011-10-23 00:00:00.000    A班第四个    2011-10-23 00:00:00.000    B班 --添加第五个    2011-10-23 00:00:00.000    A班 --添加第五个    2011-10-23 00:00:00.000    B班

结果贴出来，也就一目了然...

[解决办法]

SQL code

select a.[name],a.[time],b.[Class]from (select distinct [name],[time]from table1) a cross join (select distinct [Class]from table1) b
[解决办法]
SQL codecreate table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10))INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第三个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第四个','2011-10-23','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-23','B班')goselect name,a.time,b.Class  from ((select distinct name,time from Table1)a cross join (select time,class from Table1)b  )where a.time=b.timegroup by  name,a.time,b.Class order by  CHARINDEX(name,'第一个第二个第三个第四个第五个')drop table Table1 /*name       time                    Class---------- ----------------------- ----------第一个        2011-10-24 00:00:00.000 A班第一个        2011-10-24 00:00:00.000 B班第二个        2011-10-24 00:00:00.000 A班第二个        2011-10-24 00:00:00.000 B班第三个        2011-10-24 00:00:00.000 A班第三个        2011-10-24 00:00:00.000 B班第四个        2011-10-23 00:00:00.000 A班第四个        2011-10-23 00:00:00.000 B班第五个        2011-10-23 00:00:00.000 A班第五个        2011-10-23 00:00:00.000 B班第五个        2011-10-24 00:00:00.000 A班第五个        2011-10-24 00:00:00.000 B班(12 行受影响)*/
[解决办法]
SQL codecreate table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10))INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第三个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第四个','2011-10-23','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-23','B班')goinsert into [Table1]select * from (select distinct name,[time] from [Table1]) a,(select distinct [class] from [Table1]) bexceptselect * from [Table1]select * from [Table1] order by  CHARINDEX(name,'第一个第二个第三个第四个第五个')/*name       time                    Class---------- ----------------------- ----------第一个        2011-10-24 00:00:00.000 A班第一个        2011-10-24 00:00:00.000 B班第二个        2011-10-24 00:00:00.000 A班第二个        2011-10-24 00:00:00.000 B班第三个        2011-10-24 00:00:00.000 A班第三个        2011-10-24 00:00:00.000 B班第四个        2011-10-23 00:00:00.000 B班第四个        2011-10-23 00:00:00.000 A班第五个        2011-10-23 00:00:00.000 B班第五个        2011-10-23 00:00:00.000 A班第五个        2011-10-24 00:00:00.000 B班第五个        2011-10-24 00:00:00.000 A班(12 行受影响) 
 
[解决办法]
SQL code--简化一下 去掉连接create table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10))INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第三个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第四个','2011-10-23','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-23','B班')goselect * into #tfrom(select name,a.time,b.Class  from ((select distinct name,time from Table1)a cross join (select class from Table1)b  )group by  name,a.time,b.Class )tselect * from #tdrop table Table1 ,#t/*name       time                    Class---------- ----------------------- ----------第二个        2011-10-24 00:00:00.000 A班第二个        2011-10-24 00:00:00.000 B班第三个        2011-10-24 00:00:00.000 A班第三个        2011-10-24 00:00:00.000 B班第四个        2011-10-23 00:00:00.000 A班第四个        2011-10-23 00:00:00.000 B班第五个        2011-10-23 00:00:00.000 A班第五个        2011-10-23 00:00:00.000 B班第五个        2011-10-24 00:00:00.000 A班第五个        2011-10-24 00:00:00.000 B班第一个        2011-10-24 00:00:00.000 A班第一个        2011-10-24 00:00:00.000 B班(12 行受影响)*/
[解决办法]
SQL codecreate table [Table1]( [name] nvarchar(10),[time] datetime,[Class] nvarchar(10))INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第一个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第二个','2011-10-24','B班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第三个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-24','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第四个','2011-10-23','A班')INSERT INTO [Table1]([name],[time],[Class])VALUES('第五个','2011-10-23','B班')goINSERT [Table1]SELECT a.NAME,a.[time],b.[Class]FROM     (select [name],[time]    from Table1    GROUP BY [name],[time]    HAVING COUNT(1)=1)a,    (SELECT [Class]='A班' UNION ALL SELECT [Class]='B班')bWHERE NOT EXISTS(SELECT 1 FROM Table1 WHERE [name]=a.[name]  AND [time]=a.[time] AND [Class]=b.[Class])