带有内部连接和子查询的mysql限制

Question

我有以下查询:

SELECT saturday_combinations.index, v.val AS  `row` , COUNT( * )  AS  `count` 
FROM saturday_combinations  
INNER JOIN (

SELECT ONE AS val
FROM saturday_combinations 
WHERE ONE IS NOT NULL 

UNION 
SELECT TWO AS val
FROM saturday_combinations
WHERE TWO IS NOT NULL 

UNION 
SELECT THREE AS val
FROM saturday_combinations
WHERE THREE IS NOT NULL 

UNION 
SELECT FOUR AS val
FROM saturday_combinations
WHERE FOUR IS NOT NULL 

UNION 
SELECT FIVE AS val
FROM saturday_combinations
WHERE FIVE IS NOT NULL


UNION 
SELECT SIX AS val
FROM saturday_combinations
WHERE SIX IS NOT NULL 

UNION 
SELECT SEVEN AS val
FROM saturday_combinations
WHERE SEVEN IS NOT NULL 

) v  ON v.val = saturday_combinations.ONE
  OR v.val = saturday_combinations.TWO
  OR v.val = saturday_combinations.THREE
  OR v.val = saturday_combinations.FOUR
  OR v.val = saturday_combinations.FIVE
  OR v.val = saturday_combinations.SIX
  OR v.val = saturday_combinations.SEVEN 
  GROUP BY v.val 

查询的目的是提供表 saturday_combinations 中的列 ONE、TWO、THREE、FOUR、FIVE、SIX 和 SEVEN 中包含的不同值的计数。但是我想将 desc 限制设置为 4，以便它仅根据最后 4 行(最后四个最大索引)执行计数。但我没有让它与工会合作。在最后添加 order 和 limit 只会限制最终选择，而不是获取最后 4 行并计算它们的分布。有什么建议吗？

表架构如下:

index | ONE|TWO|THREE|FOUR|FIVE|SIX|SEVEN
 1       1   3   7     10    11  12  13
 2       3   4   5     30    31  22  23
 3       1   2   3      4     5   6   7
 4       1   2   3      4     5   6   7
 5       1   2   3      4     5   6   7
 6       1   2   3      4     5   6   7
 7       1   2   3      4     5   6   7
 8       1   2   3      4     5   6   7
 9       1   2   3      4     5   6   7
 10      1   2   3      4     5   6   7

索引是自动递增的，ONE-SEVEN 是整数值。

表中大约有 3000 行，我想根据最后 n 行计算每个值的出现次数。

Ideal result for the last n rows where n = last 3 rows should be
Numbers|Count
   1       3
   2       3
   3       3
   4       3
   5       3
   6       3
   7       3

如果我增加 n 以包括最后 6 行，它们的计数应该增加。如果我可以持续 10 行，计数应该增加，并且其他数字应该与它们的计数一起出现。

这里是真实表格示例的链接。 http://sqlfiddle.com/#!2/d035b

Answer 1

如果对我的评论的回答是 yes 那么，您可以尝试以下操作。当您需要将 limit, order by 添加到 union selects 时，您需要将 union queries 用方括号 () 包裹起来.

• SQLFIDDLE DEMO

代码:

(SELECT ONE AS val
FROM saturday_combinations 
WHERE ONE IS NOT NULL 
order by ONE desc limit 4)

UNION 
(SELECT TWO AS val
FROM saturday_combinations
WHERE TWO IS NOT NULL 
order by TWO desc limit 4)

UNION 
(SELECT THREE AS val
FROM saturday_combinations
WHERE THREE IS NOT NULL 
order by THREE desc limit 4)

如果对我的评论的回答是否，那么请澄清。

这是基于您的示例日期的代码:

select distinct x.one as uniqunumbers,
count(x.one) as counts
from(
sELECT DISTINCT 'one' 
AS col1, one FROM sat_comb
UNION ALL
SELECT DISTINCT 'two' 
AS col1, two FROM sat_comb
UNION ALL
SELECT DISTINCT 'three' 
AS col1, three FROM sat_comb
) as x
group by x.one;

UNIQUNUMBERS    COUNTS
1               1
3               2
4               1
5               1
7               1

根据 OP 进行的编辑已澄清并更新了问题。

引用:“但是我想对其进行限制，以便它首先获取最后 n 行，然后对这 n 行中的值进行计数。这意味着，如果我有 3 列 3000 行和 35这 3000 行中随机出现的整数，它应该计算每个整数出现的次数。”

• SQLFIDDLE DEMO2

查询:

select x.one as uniqunumbers,
    count(x.one) as counts
    from(
    (sELECT DISTINCT 'one' 
    AS col1, one FROM sat_comb
     order by id desc limit 4)
    UNION ALL
    (SELECT DISTINCT 'two' 
    AS col1, two FROM sat_comb
     order by id desc limit 4)
    UNION ALL
    (SELECT DISTINCT 'three' 
    AS col1, three FROM sat_comb
     order by id desc limit 4)
      UNION ALL
    (SELECT DISTINCT 'four' 
    AS col1, four FROM sat_comb
     order by id desc limit 4)
      UNION ALL
    (SELECT DISTINCT 'five' 
    AS col1, five FROM sat_comb
     order by id desc limit 4)
      UNION ALL
    (SELECT DISTINCT 'six' 
    AS col1, six FROM sat_comb
     order by id desc limit 4)
      UNION ALL
    (SELECT DISTINCT 'seven' 
    AS col1, seven FROM sat_comb
     order by id desc limit 4)
    ) as x
    group by x.one;

输出:

UNIQUNUMBERS    COUNTS
2               4
3               3
4               3
5               4
6               4
8               3
9               4
20              3