SELECT
type
, count(*)
, count(DISTINCT u)
, count(CASE WHEN plat=1 THEN u ELSE NULL)
, count(DISTINCT CASE WHEN plat=1 THEN u ELSE NULL)
, count(CASE WHEN (type=2 OR type=6) THEN u ELSE NULL)
, count(DISTINCT CASE WHEN (type=2 OR type=6) THEN u ELSE NULL)
FROM
t
WHERE
dt in ("2012-1-12-02", "2012-1-12-03")
GROUP BY
type
ORDER BY
type
;
how about the Efficiency of sql execution?
在 2012-01-12 22:20:51，"Martin Kuhn" <[email protected]> 写道：
>Hi there,
>
>> Select count(*), count(distinct u), type from t group by type where plat=1
>> and dt=”2012-1-12-02”
>> Select count(*), count(distinct u), type from t where (type =2 or type =6)
>> and dt=”2012-1-12-02” group by type;
>
>> Is there a better way to do these queries?
>
>You could try something like this:
>
>SELECT
> type
> , count(*)
> , count(DISTINCT u)
> , count(CASE WHEN plat=1 THEN u ELSE NULL)
> , count(DISTINCT CASE WHEN plat=1 THEN u ELSE NULL)
> , count(CASE WHEN (type=2 OR type=6) THEN u ELSE NULL)
> , count(DISTINCT CASE WHEN (type=2 OR type=6) THEN u ELSE NULL)
>FROM
> t
>WHERE
> dt in ("2012-1-12-02", "2012-1-12-03")
>GROUP BY
> type
>ORDER BY
> type
>;
>
>Good luck :)
>Martin Kuhn
>
>
>P.S. You'ge got a strange date format there. For sorting purposes it would be
>more appropriate to use something like "2012-01-12-02".
>
