doujiang1001 2017-07-30 21:53
浏览 105
已采纳

在一个查询中选择*,count(*)[重复]

This question already has an answer here:

I've got a DB with a few columns and I'm trying to populate a html table with it. Everything's going fine but I've encountered the following problem:

Since I'm filling filtered Results into different Columns, I came up with a SQL Query that needs both Select * and count(*)?

$query = "SELECT *, COUNT(example_A) AS total_example_A FROM test WHERE example_A = 'certain_result' AND date(start_date) = '$current_date_proof' ORDER BY start_date ASC";

It does work, but I'm only getting the first result. I guess I cannot combine Select with Count?

</div>
  • 写回答

1条回答 默认 最新

  • dtwknzk3764 2017-07-30 22:05
    关注

    You can do it with a correlated sub-query, Count is an aggregation function ( so it aggregates or combines all the data ):

    $query = "
        SELECT
            t1.*,
            ( SELECT COUNT(t0.id) FROM test AS t0 WHERE t0.id = t1.id ) AS total_example_A
        FROM
            test AS t1
        WHERE
                t1.example_A = 'certain_result'
            AND
                date(t1.start_date) = '$current_date_proof'
        ORDER BY t1.start_date ASC
    ";
    

    This assumes that your table test has a primary key named id. One other thing is I would count on the primary key if its not (example_A) COUNT(t0.id)

    In my world a database either have a Auto Increment Int as the primary key or they have a compound primary key consisting of 2 or more foreign keys which are themselves Auto Increment Int fields. It's vital ( IMO ) to always have a surrogate key in you table. That is a key that has no direct relationship to the data itself. But, that is just me...

    You could just count the return within your application, but barring that the correlated sub-query should give you the best/goodest performance. Certainly much better then a separate database call.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题