选择,计数和在哪里使用codeigniter和mysql [英] Select, count and where using codeigniter and mysql
本文介绍了选择,计数和在哪里使用codeigniter和mysql的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个表如下:
- tblSaler
SalerID | SalerName |
----------------------|
1 | sothorn |
----------------------|
2 | Daly |
----------------------|
3 | Lyhong |
----------------------|
4 | Chantra |
----------------------|
- tblProduct
ProductID | Product | SalerID |
--------------------------------|
1 | Pen | 3 |
--------------------------------|
2 | Book | 2 |
--------------------------------|
3 | Phone | 3 |
--------------------------------|
4 | Computer | 1 |
--------------------------------|
5 | Bag | 3 |
--------------------------------|
6 | Watch | 2 |
--------------------------------|
7 | Glasses | 4 |
--------------------------------|
我需要的结果是:
sothorn | 1
Daly | 2
Lyhong | 3
Chantra | 1
我尝试过:
$this->db->select('count(SalerName) as sothorn where tblSaler.SalerID = 1, count(SalerName) as Daly where tblSaler.SalerID = 2, count(SalerName) as Lyhong where tblSaler.SalerID = 3, count(SalerName) as Chantra where tblSaler.SalerID = 4');
$this->db->from('tblSaler');
$this->db->join('tblProduct', 'tblSaler.SalerID = tblProduct.SalerID');
推荐答案
You can use this query for this
SELECT
tblSaler.SalerName,
count(tblProduct.ProductID) as Total
FROM tblSaler
LEFT JOIN tblProduct
ON tblProduct.SalerID = tblSaler.SalerID
GROUP BY tblSaler.SalerID
$ b b
此处是此活动记录
And here is the active record for this
$select = array(
'tblSaler.SalerName',
'count(tblProduct.ProductID) as Total'
);
$this->db
->select($select)
->from('tblSaler')
->join('tblProduct','roduct.SalerID = tblSaler.SalerID','left')
->group_by('tblSaler.SalerID')
->get()
->result_array();
OUTPUT
| SALERNAME | TOTAL |
|-----------|-------|
| sothorn | 1 |
| Daly | 2 |
| Lyhong | 3 |
| Chantra | 1 |
这篇关于选择,计数和在哪里使用codeigniter和mysql的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文