1select2 p.product_id
3from4 dtb_products as p
5innerjoin6 dtb_products_class as pc
7on8 p.product_id = pc.product_id
9groupby10 p.product_id
11having12count(distinct pc.del_flg)=1
1SELECTDISTINCT p.*2FROM dtb_products AS p
3LEFTOUTERJOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id AND pc_1.del_flg =14LEFTOUTERJOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg =05WHERE pc_1.del_flg ISNOTNULL6AND pc_0.del_flg ISNULL;
もしくは
sql
1SELECTDISTINCT p.*2FROM dtb_products AS p
3INNERJOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id
4LEFTOUTERJOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg =05WHERE pc_1.del_flg =16AND pc_0.del_flg ISNULL;
実行結果
sql
1mysql>SELECT*FROM dtb_products;2+------------+3| product_id |4+------------+5|100|6|200|7|300|8|400|9+------------+104rowsinset(0.00 sec)1112mysql>SELECT*FROM dtb_products_class;13+------------+---------+14| product_id | del_flg |15+------------+---------+16|100|1|17|100|0|18|100|0|19|100|0|20|100|1|21|200|1|22|200|1|23|200|1|24|200|1|25|200|1|26|300|0|27|300|0|28+------------+---------+2912rowsinset(0.00 sec)3031mysql>SELECTDISTINCT p.*32->FROM dtb_products AS p
33->LEFTOUTERJOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id AND pc_1.del_flg =134->LEFTOUTERJOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg =035->WHERE pc_1.del_flg ISNOTNULL36->AND pc_0.del_flg ISNULL;37+------------+38| product_id |39+------------+40|200|41+------------+421rowinset(0.00 sec)4344mysql>SELECTDISTINCT p.*45->FROM dtb_products AS p
46->INNERJOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id
47->LEFTOUTERJOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg =048->WHERE pc_1.del_flg =149->AND pc_0.del_flg ISNULL;50+------------+51| product_id |52+------------+53|200|54+------------+551rowinset(0.00 sec)
1select product_id
2from3(4select5 p.product_id as product_id,6 pc.del_flg as del_flg
7from8 dtb_products as p
9innerjoin10 dtb_products_class as pc
11on12 p.product_id = pc.product_id
13groupby14 p.product_id
15having16count(distinct pc.del_flg)=117)as a
18where del_flg =119;
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/02 03:00
2017/08/02 04:08