数据库结构:
CREATE TABLE IF NOT EXISTS `phpa_ssq` (
`num1` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num2` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num3` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num4` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num5` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num6` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;对应记录:
num1 num2 num3 num4 num5 num6
100 120 150 170 200 500
56 100 120 170 220 280我想统计:100,500这两个数字在所有记录中出现的记录。如何写sql呢?如第一条记录就符合,num1为100,num6为500,而第二条记录则不符合。
CREATE TABLE IF NOT EXISTS `phpa_ssq` (
`num1` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num2` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num3` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num4` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num5` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
`num6` varchar(2) COLLATE utf8_bin NOT NULL DEFAULT '',
PRIMARY KEY (`number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;对应记录:
num1 num2 num3 num4 num5 num6
100 120 150 170 200 500
56 100 120 170 220 280我想统计:100,500这两个数字在所有记录中出现的记录。如何写sql呢?如第一条记录就符合,num1为100,num6为500,而第二条记录则不符合。
from phpa_ssq
where num1='500'
or num2='500'
or num3='500'
or num4='500'
or num5='500'
or num6='500'
find_in_set('500',concat_ws(',',num1,num2,num3,num4,num5,num6) );
where
instr(concat(num1,',',num2,',',num3,',',num4,',',num5,',',num6),
'100')>0
and
instr(concat(num1,',',num2,',',num3,',',num4,',',num5,',',num6)
,'500')>0;
-> find_in_set(500,concat_ws(',',num1,num2,num3,num4,num5,num6) );
+------+------+------+------+------+------+
| num1 | num2 | num3 | num4 | num5 | num6 |
+------+------+------+------+------+------+
| 100 | 120 | 150 | 170 | 200 | 500 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
from phpa_ssq
where (num1='500'
or num2='500'
or num3='500'
or num4='500'
or num5='500'
or num6='500'
)
and (num1='100'
or num2='100'
or num3='100'
or num4='100'
or num5='100'
or num6='100'
)
where (0+num1=500
or 0+num2=500
or 0+num3=500
or 0+num4=500
or 0+num5=500
or 0+num6=500
)
and (
0+num1=100
or 0+num2=100
or 0+num3=100
or 0+num4=100
or 0+num5=100
or 0+num6=100
)