SQL查询返回基于vaule

Question

我试图得到一个SQL查询返回一组基于AA int值的值设置为1

Date   Name    Split   ID 
2014-09-02 Harry Potter   1   23 
2014-09-02 Harry Potter   1   434 
2014-09-02 Michael Jinks  0   24 
2014-09-02 Sam Smith   1   12 
2014-09-02 Sam Smith   1   244 
2014-09-02 Kelly Jane   0   124 
2014-09-03 Harry Potter   1   23 
2014-09-03 Harry Potter   1   434 

我希望它只返回一个值仅记录集从每次使用哈利波特的用户记录，如果拆分设置为“1”，则忽略第二个记录ID

它需要是if语句，如果Split =“1”然后查找最高记录和返回值，但没有我可以找到可以做到这一点。

我曾尝试

select distinct * from LOG where split = 1 

应该返回像这样

Date   Name    Split   ID 
2014-09-02 Harry Potter   1   23 
2014-09-02 Michael Jinks  0   24 
2014-09-02 Sam Smith   1   12 
2014-09-02 Kelly Jane   0   124 
2014-09-03 Harry Potter   1   23 

Answer 1

这个怎么样？

create table #temp(
    [date] smalldatetime, 
    name varchar(100), 
    split int, 
    id int 
) 
insert into #temp 
select '2014-09-02', 'Harry Potter', 1, 23 union all 
select '2014-09-02', 'Harry Potter', 1, 434 union all 
select '2014-09-02', 'Michael Jinks', 0, 24 union all 
select '2014-09-02', 'Sam Smith', 1, 12 union all 
select '2014-09-02', 'Sam Smith', 1, 244 union all 
select '2014-09-02', 'Kelly Jane', 0, 124 union all 
select '2014-09-03', 'Harry Potter', 1, 23 union all 
select '2014-09-03', 'Harry Potter', 1, 434 

-- Start 
;with cte as(
    select 
     *, 
     row_number() over(partition by name order by [date], id) as rn -- For each name display first record with earliest date and lowest id 
     --row_number() over(partition by name, [date] order by id) as rn -- For each name/date combination display first record with lowest id 
    from #temp -- replace with your table name 
) 
select 
    [date], 
    name, 
    split, 
    id 
from cte 
where 
    split = 1 
    and rn = 1 
-- End 
drop table #temp 

Answer 2

如果你感兴趣的ID始终是最低的ID，然后为新表的问题

SELECT Date, Name, Split, MIN(ID) 
FROM log 
GROUP BY Date, Name, Split 

更新应答。

SELECT Date, Name, Split, MIN(ID) as ID 
INTO tablename 
FROM log 
GROUP BY Date, Name, Split