简单SQL语句 提问 请帮忙....

Question

select customername,max(createdate)
from table1
group by customername

Answer 1

select CustomerName,CreateDate from table1 t1,(select CustomerName,max(CreateDate) from table1 group by  CustomerName) t2 where t1.CustomerName=t2.CustomerName and t1.CreateDate=t2.CreateDate

Answer 2

honestface(哗拉拉) 
可以解释一下么?

Answer 3

如果
table2:
 CustomerName   CreateDate   Column3
  a             2004-11-7    345
  a             2004-11-6    345658
  b             2003-11-7    8574
  b             2004-11-7    789
查询结果:
 CustomerName   CreateDate
  a             2004-11-7
  b             2004-11-7xian现在的SQL语句应该怎么写呢?

Answer 4

不好意思,上面更新为:
如果
table2:
 CustomerName   CreateDate   Column3
  a             2004-11-7    345
  a             2004-11-6    345658
  b             2003-11-7    8574
  b             2004-11-7    789
查询结果:
 CustomerName   Column3
  a             345
  b             789现在的SQL语句应该怎么写呢?

Answer 5

select customerName,Min(column3) from table2 order by customerName

Answer 6

select customername,max(createdate)
from table1
group by customername

Answer 7

select customerName,Min(column3) as column3 from table2 order by customerName

Answer 8

如果
table2:
 CustomerName   CreateDate   Column3
  a             2004-11-7    345
  a             2004-11-6    345658
  b             2003-11-7    8574
  b             2004-11-7    789456
查询结果:
 CustomerName   Column3
  a             345
  b             789456
(这里的查询根据还是CreateDate)
SQL应该如何写?

Answer 9

select CustomerName,Column3
from table2  aa
where CreateDate=(select top 1 createdate 
                  from table2 
                  where CustomerName=aa.CustomerName)

Answer 10

select customerName,column3 from table2  where CreateDate=(select max(createdate)
from table2 )order by customerName

Answer 11

lsxaa(小李铅笔刀)-->
select CustomerName,Column3
from table2  aa
where CreateDate=(select top 1 createdate 
                  from table2 
                  where CustomerName=aa.CustomerName)这个语句比较接近,但是没有实现..
我试过后,发现要加上这句才可以:ORDER BY createtime DESC
全句为:
select CustomerName,Column3
from table2  aa
where CreateDate=(select top 1 createdate 
                  from table2 
                  where CustomerName=aa.CustomerName
                  ORDER BY createtime DESC)

Answer 12

select a.CustomerName,
       (select top 1 b.Column3 
          from table1 b 
         where a.CustomerName = b.CustomerName 
         order by CreateDate desc ) as Column3 
  from (select CustomerName 
          from table1 
         group by CustomerName) a 

Answer 13

select CustomerName,Column3
from table2  aa
where CreateDate=(select top 1 createdate 
                  from table2 
                  where CustomerName=aa.CustomerName
                  ORDER BY createtime DESC)

Answer 14

THX   lsxaa(小李铅笔刀) victorycyz(中海) ....
----------------------------
这是我的一个扩展问题...
如果现在SQL语句写成这样:
select a.CustomerName,
       (select top 1 b.Column3
          from (select *
from table2) b 
         where a.CustomerName= b.CustomerName
         order by CreateDate   desc ) as Column3
  from (select CustomerName
          from (select *
from table2) c
         group by CustomerName) a 你会发现,其中出现了两个(select *
       from table2)
我可不可以只使用其中一句 建表d ,另一句直接直接调用表d ?
谢谢!

Answer 15

select CustomerName,  max(CreateDate)
from t
group by CustomerName

Answer 16

表d 是什么?select a.CustomerName,
       (select top 1 b.Column3
          from  table2  b  --这里直接这样写
         where a.CustomerName= b.CustomerName
         order by CreateDate   desc ) as Column3
  from (select CustomerName
          from table2  c   --这里直接这样写
         group by CustomerName) a

Answer 17

select CustomerName,Column3
from table2  aa
where CreateDate=(select top 1 createdate 
                  from table2 
                  where CustomerName=aa.CustomerName
                  ORDER BY createtime DESC)

Answer 18

create table table2(CustomerName nvarchar(5),CreateDate datetime,Column3 int)insert into table2 SELECT 'a',             '2004-11-7',    345
union all SELECT  'a',             '2004-11-6',    345658
union all SELECT  'b',             '2003-11-7',    8574
union all SELECT  'b',             '2004-11-7',    789456
union all SELECT  'b',             '2004-11-7',    78945
go
SELECT CustomerName,Column3 FROM table2 b
WHERE CreateDate in (SELECT TOP 1 CreateDate FROM table2 
                     WHERE CustomerName=b.CustomerName
                     ORDER BY CreateDate DESC
                     )
a 345
b 789456
b 78945