获取非空值的计数

我到处都看过如何做到这一点,看起来输出1号码的方法似乎很糟糕。< / p>

这是我的数据库的布局:</ p>

  UserId Info1 Info2 Info3 Info4 Info5 Info6 Info7 


user1 | xyz | | abc | xyz | | abc |

user2 | | abc | | def | ghi | wxy | jkl

我想要做的是返回一个例如:

User1已完成4个任务
User2已完成5个任务
</ code> </ pre>

有95个 这一行中的列,以便您可以理解,单独计算每个列将是一个真正的痛苦。 但是我只想计算那些非空的并返回一个数字。</ p>

 &lt;?
$ sql =“SELECT * FROM builder WHERE userid ='”。 $ userid。“'IS NOT NULL”;
$ query = mysql_query($ sql);

$ num = mysql_num_fields($ query);
echo $ num;?&gt;
</ code> </ pre>

这将输出总共有多少行,无论它们是否是 是或否,所以数字总是相同的,这不是我想要的。</ p>

有任何建议吗?</ p>
</ div>

展开原文

原文

I've looked everywhere on how to do this and it seems like an awful long approach to output 1 number.

This is the layout of my database:

UserId         Info1    Info2    Info3   Info4   Info5   Info6   Info7
-------------------------------------------------------------------------------

user1      |   xyz   |         | abc   |  xyz  |       | abc   | 
user2      |         |  abc    |       |  def  |  ghi  | wxy   | jkl

What i want to do is return a number for example: 

User1 has completed 4 Tasks 
User2 has completed 5 Tasks       

There are 95 columns in this row so as you can understand it would be a real pain to count each one individually. However i only want to count the ones that are not empty and return a number.

<?
$sql="SELECT * FROM builder WHERE userid = '".$userid."' IS NOT NULL";
$query=mysql_query($sql);    
$num=mysql_num_fields($query);
echo $num;?>

This outputs how many rows there are in total regardless of whether they are empty or not so the number is always the same which isn't what I want.

Any suggestions?

duanbinren8906
duanbinren8906 也许我错过了什么。该代码中的实际mysql_query部分在哪里。当我把它放入网站它只是打破了布局大声笑。
5 年多之前 回复
dongxuan1660
dongxuan1660 有关该Ghost代码的任何提示?
5 年多之前 回复
dongpou7275
dongpou7275 只需使用while循环,在其中循环所有行并检查它是否为空,增加计数器
5 年多之前 回复

2个回答



您可以使用此结构计算MySQL中的值:</ p>

  select((  info1不为空)+ 
(info2不为空)+
(info3不为空)+
(info4不为空)+
(info5不为空)+
(info6不为空) +
(info7不为空)
)as NumNotNull
</ code> </ pre>

MySQL在数字上下文中将布尔表达式视为整数。 TRUE为“1”,因此您可以将它们相加以获得匹配的总数。</ p>
</ div>

展开原文

原文

You can count the values in MySQL using this construct:

select ((info1 is not null) +
        (info2 is not null) +
        (info3 is not null) +
        (info4 is not null) +
        (info5 is not null) +
        (info6 is not null) +
        (info7 is not null)
       ) as NumNotNull

MySQL treats boolean expressions as integers, in a numeric context. TRUE is "1", so you can just add them up to get the total that match.

dras2334
dras2334 谢谢你的回复。 最后我做了一个非空的连接表来解决:)
4 年多之前 回复
duanne9313
duanne9313 。 。 。 是。 您可以使用information_schema.columns来获取列。 如果您手动执行此操作,则可以在电子表格中轻松生成代码。 在应用程序中,您可以编写PHP代码来执行相同的操作。
5 年多之前 回复
douci1677
douci1677 所以这意味着我需要将所有95列添加到这个是吗?
5 年多之前 回复



我为这里的部分答案道歉(即我没有写出建议的存储过程)但也许这将是一个开始 您。 我的方法需要一些时间来编写,但如果有时将新任务添加为构建器表中的新列,则会特别有用。</ p>

方法如下:创建一个支持的存储过程 你的数据。 然后查询该过程生成的结果集,以便查询如下所示的数据集:</ p>

  userID任务完成


user1 Info1 True
user1 Info2 False
user1 Info3 True
user1 Info4 True
user1 Info5 False
user1 Info6 True
user1 Info7 False
user2 Info1 False
user2 Info2 True
user2 Info3 False
user2 Info4 True
user2 Info5 True
user2 Info6 True
user2 Info7 True
</ code> </ pre>

我不相信MySQL有一个转轴命令,但你可以模仿一个 通过编写一个存储过程来动态生成SQL语句,因为它会读取内置的INFORMATION_SCHEMA COLUMNS表。 </ p>

动态SQL看起来像:</ p>

  SELECT userID,'Info1'AS Task,info1 IS NOT NULL FROM builder 
UNION

SELECT userID,'Info2'AS Task,info2 IS NOT NULL FROM builder
UNION
...
</ code> </ pre>

你必须玩这一段时间 但我认为它会给你一个更有用的结果。 顺便说一句,如果可以重新设计构建器表,那么看起来像这个存储过程返回的设计可能更容易使用。</ p>
</ div>

展开原文

原文

I apologize for the partial answer here (i.e. I haven't written out the proposed stored procedure) but perhaps this will be a start for you. My approach will take some time to write but will be particularly useful if new tasks are sometimes added as new columns in your builder table.

The approach is as follows: create a stored procedure that pivots your data. You then query off the result set generated by that procedure so that you query off a data set that looks like the following:

userID  task   Complete
------  ----   --------
user1   Info1  True
user1   Info2  False
user1   Info3  True
user1   Info4  True
user1   Info5  False
user1   Info6  True
user1   Info7  False
user2   Info1  False
user2   Info2  True
user2   Info3  False
user2   Info4  True
user2   Info5  True
user2   Info6  True
user2   Info7  True

I don't believe MySQL has a pivot command but you can imitate one by writing a stored procedure that dynamically generates an SQL statement as it reads off the built in INFORMATION_SCHEMA COLUMNS Table.

The dynamic SQL would look something like:

SELECT userID, 'Info1' AS Task, info1 IS NOT NULL FROM builder
UNION
SELECT userID, 'Info2' AS Task, info2 IS NOT NULL FROM builder
UNION
...

You'd have to play with this a while but I think it will give you a result that could be more useful. BTW, if it's possible to redesign the builder table, a design that looks like what this stored procedure returns might be easier to work with.

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐