sql: case when, distinct, group by, group_concat

最近做了个报表，痛苦了半年，终于搞出来了，知道了一些sql用法,

如：

case colom when colom then expr1 else expr2 end, //类似switch case可以有多个when

distinct, group by,//都是取出重复记录的，但是distinct无法显示不重复的其他记录。

mysql的 group_concat(colom SEPARATOR ',') 将字符串字段以分隔符号相连

 

下面是一张机票的表(airTicket)

 

     出发地 到达   往返         机票状态    价格

-------------------------------------------------

id   from    to    back       status       price

----------------------------------------------------

1   a         b      true      confirm   800

----------------------------------------------------

2   c         d      false     cancle     600

----------------------------------------------------

3   c         f      true      cancle     400

----------------------------------------------------

4   a         c      false     confirm    300

 

假设要统计不同状态下的机票的总价格和去过的不同地区,去过的地区就是to的地区，如果是往返的机票，from的也包括。

我的实现如下：

 

select status, count(id), sum(price), group_concat(to),

group_concat(case back when back then from else to end)

from airTicket

group by status; 

 

result：

 

cancel      2    1000      d,f         d,c

-------------------------------------------

confirm    2     1100      b,c        a,c

 

得到上面的字符串之后在计算去过的不同的地区就可以将两个段链接起来，split然后去掉重复的就知道有多少个了。

 

cancel = 3  confirm = 3

 

其实上面语句最复杂的就是统计地区了，需要根据是否是往返机票（back）合并出发地（from）和到达地（to），并且统计出不同的地区。我能实现的方式就是这样了，而且使用了mysql特有的group_concat 方法，如果谁能够替代此方法，请一定回个帖子。

 

通过MySQLDialect 方式让hibernate支持mysql的group_concat

http://hi.baidu.com/ayu1106/blog/item/4da6810efd140ae237d122f3.html

 

==========================================

 

对于distinct和group by是这样一个类似版本管理的记录

 

 

firename         version

-------------------------------

a                      1

-------------------------------

b                      1

-------------------------------

a                      2

 

现在想找到每个文件的最近版本结果如下：

 

a                 2

------------------

b                 1

 

如果使用distinct 可以

select distinct firename from table order by firename, version DESC;

这样只能显示出来firename一列，如果想显示多列，那么可以通过 

 

select firename, version from table t where version =

(select max(version) from table where firename = t.firename)

group by firename

 

其实有高人已经总结了很多方式：

 

http://www.cnblogs.com/kangtr/archive/2008/02/25/1080278.html