【Django】Django 如何实现 如下 联表 JOIN 查询?
django 实现 如何 查询 Join 如下 联表
2023-09-27 14:25:07 时间
SQL语句:
select distinct a.device_hash, sum(b.cmn_merge_count) from (select distinct device_hash from tbl_fileprotect_svc_instance where customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e') as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash group by a.device_hash;
简化SQL如下(把子查询转化为WHERE语句):
select distinct a.device_hash, sum(b.cmn_merge_count) from tbl_fileprotect_svc_instance as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e' group by a.device_hash;
select distinct a.device_hash, sum(b.cmn_merge_count) from tbl_fileprotect_svc_instance as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e' group by a.device_hash;
select distinct a.device_hash, sum(b.cmn_merge_count) as alarm_sum from tbl_fileprotect_svc_instance as a left join tbl_file_protect_alarm as b on a.device_hash = b.device_hash where a.customer_id='3f500ac5-020d-3ce3-a2a2-51a59ddd606e' group by a.device_hash;
Django代码:
TODO:
方法一:
ret = [] device_list = models.FILE_PROTECT_INSTANCE.objects.filter(customer_id=customer_id) for item in device_list: tmp_dict = {} tmp_dict['device_hash'] = item.device_hash tmp_dict['hostname'] = item.hostname tmp_dict['status'] = item.status from django.db.models import Sum alarm_sum_group_items = models.FILE_PROTECT_ALARM.objects.filter(customer_id=customer_id).filter(device_hash=tmp_dict['device_hash']).values('device_hash').annotate(alarm_sum=Sum('cmn_merge_count')) #tmp_dict['customer_id'] = customer_id tmp_dict['alarm_sum'] = alarm_sum_group_items[0]['alarm_sum'] if (len(alarm_sum_group_items)!=0) else 0 ret.append(tmp_dict)
方法二: hashes = A.objects.values_list("device_hash", flat=True).filter(customer_id="3f500ac5-020d-3ce3-a2a2-51a59ddd606e") B.objects.filter(device_hash__in=hashes).values_list("device_hash").annotate(Sum("cmn_merge_count"))
SQL实现聚合查询统计(Sum,Count等)
from django.db.models import Sum #start_time = datetime.utcfromtimestamp(data['start_time']).replace(tzinfo=utc) #end_time = datetime.utcfromtimestamp(data['end_time']).replace(tzinfo=utc) end_time = datetime.now() start_time = end_time + timedelta(days=-1) condition['cmn_alert_time__range'] = (start_time, end_time) #alarm_sum_group_items_day = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_day=Sum('cmn_merge_count')) alarm_sum_day = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count")) day_sum = alarm_sum_day['cmn_merge_count__sum'] if (alarm_sum_day['cmn_merge_count__sum'] is not None) else 0 end_time = datetime.now() start_time = end_time + timedelta(days=-7) condition['cmn_alert_time__range'] = (start_time, end_time) #alarm_sum_group_items_week = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_week=Sum('cmn_merge_count')) alarm_sum_week = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count")) week_sum = alarm_sum_week['cmn_merge_count__sum'] if (alarm_sum_week['cmn_merge_count__sum'] is not None) else 0 condition = {} #condition['device_hash'] = data['device_hash'] condition['customer_id'] = customer_id end_time = datetime.now() #start_time = end_time + timedelta(days=-7) condition['cmn_alert_time__lte'] = end_time #alarm_sum_group_items_all = models.FILE_PROTECT_ALARM.objects.filter(**condition).values('customer_id').annotate(alarm_sum_all=Sum('cmn_merge_count')) alarm_sum_all = models.FILE_PROTECT_ALARM.objects.filter(**condition).aggregate(Sum("cmn_merge_count")) all_sum = alarm_sum_all['cmn_merge_count__sum'] if (alarm_sum_all['cmn_merge_count__sum'] is not None) else 0
参考资料:
http://stackoverflow.com/questions/6481279/django-sum-query
参考资料:
DINSTINCT: http://www.w3school.com.cn/sql/sql_distinct.asp
RAW语句实现:http://yeelone.blog.51cto.com/1476571/946694
http://www.it165.net/database/html/201207/2755.html
http://stackoverflow.com/questions/21355601/django-orm-inner-join
http://djangobook.py3k.cn/2.0/chapter10/
http://blog.csdn.net/clh604/article/details/9789839
https://www.douban.com/note/301166150/
相关文章
- django之允许外部机器访问
- Django--缓存、信号、序列化
- Django中Q查询及Q()对象
- Django 基础篇(二)视图与模板
- 137.Django中的DebugToolBar调试工具栏介绍、配置、使用
- Django学习笔记之Queryset详解
- Django学习笔记之利用Form和Ajax实现注册功能
- Python编程:Django中间件实现登陆验证
- python编程:linux环境gunicorn+nginx部署django项目
- 【Python】Django auth 修改密码如何实现?
- 两个 Django 插件( django_extensions,django_toolbar)
- Django forms 主要的标签介绍
- django中日志使用学习记录
- Python Django 基本流程
- django schema migration
- django+appium实现UI自动化测试平台(开源部分,可定制开发)
- django 实现同一个ip十分钟内只能注册一次(redis版本)
- django 实现登录时候输入密码错误5次锁定用户十分钟
- Django static media template目录设置方法
- django rest_framework比较完整的自定义实现样例
- django rest framework如何实现nest field显示,如何保存有外键的字段更新
- 关于django rest framework里token auth的实现及答疑
- django自身提供的sitemap和feed实现样例
- 四十七、python学习之Django框架(DRF框架三):Request与Response、视图与视图集、路由