Re: how to avoid scan the same table multi times?

[Xiaobin She]

to Martin, Mark and Edward,

thank you for your advices, I will try it out.

And to Martin, by "appropriate data format", do you mean something like
"2012011202" ?

thanks!

xiaobin

在 2012年1月12日 下午10:20，Martin Kuhn <martin.k...@affinitas.de>写道：

> 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".
>
>