首页 / MYSQL / mysql限制与内部联接和子查询

mysql限制与内部联接和子查询

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql限制与内部联接和子查询，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3988字，纯文字阅读大概需要6分钟。

内容图文

我有以下查询：

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行(最后四个最大索引)执行计数.但是我没有让它与工会合作.在最后添加顺序和限制仅是最终选择的限制,而不是获得最后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

索引是自动递增的,而七分之一是整数.

该表中大约有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

解决方法:

如果对我的评论的回答是“是”,则可以尝试以下方法.当您需要添加限制时,按联合排序选择需要用方括号()包裹联合查询.

> 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行,并且在这3000行中随机出现35个整数它应该计算每个整数出现多少次.”

> 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

内容总结

以上是互联网集市为您收集整理的mysql限制与内部联接和子查询全部内容，希望文章能够帮你解决mysql限制与内部联接和子查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/883997.html

来源：【匿名】